-
Sep 27th, 2017, 01:18 PM
#1
Thread Starter
Frenzied Member
Issue with reading Access database
I have developed a small app that reads some data that is stored in a table in an Access database that is located in a network folder. The app works perfect on my dev machine with no issues accessing the data. The problem comes when other users try to run the program on their PC. It throws an exception:
"ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."
The PC's are all identical and appear to have the same odbc drivers installed.
This is my connection string:
dbConnStr = "Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq=c:\tmp\MyDbFile.accdb;Uid=Admin;Pwd=;"
And here is how I open the database:
Code:
Using con As OdbcConnection = New OdbcConnection(dbConnStr)
con.Open()
'code here to read data
End Using
I am targeting x86 if that makes any difference. Again, it works fine from my dev machine, but not from any other PC's. Can someone please advise where I need to start looking to get this resolved? Any help would be greatly appreciated. Thanks...
-
Sep 27th, 2017, 01:27 PM
#2
Hyperactive Member
Re: Issue with reading Access database
I belive you need to override "dbConnStr " on application startup to the network folder
-
Sep 27th, 2017, 01:33 PM
#3
Hyperactive Member
Re: Issue with reading Access database
Can you post the code from where you get your connection string?
Is it stored under settings?
is it in another custumised file?
-
Sep 27th, 2017, 01:45 PM
#4
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by Mike Storm
Can you post the code from where you get your connection string?
Is it stored under settings?
is it in another custumised file?
I just have the connection string hard-coded in the program for now. And the connection string contains the actual network path name. I just used a dummy filename in my example above...
-
Sep 27th, 2017, 01:53 PM
#5
Hyperactive Member
Re: Issue with reading Access database
never mind, i was not paing enought attention, you using ODBC ...
I dont know if you can simply override the connection string becouse of the "dsn" file.
Or if you will have to add a new "dsn" on the other machine.
-
Sep 27th, 2017, 01:58 PM
#6
Hyperactive Member
Re: Issue with reading Access database
I found this on another forum:
You don't really need to create an ODBC data source name for an Access database. Instead you should be using a DSN-less connection and the Jet OLEDB Provider. You can open a database on the network using a UNC path (assuming your in the same domain). Below is a connection string example:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\System\share\folder\Database.mdb;Jet OLEDB:Engine Type=4
Paul ~~~~ Microsoft MVP (Visual Basic)
https://social.msdn.microsoft.com/Fo...orum=vbgeneral
Last edited by Mike Storm; Sep 27th, 2017 at 02:01 PM.
-
Sep 27th, 2017, 02:01 PM
#7
Hyperactive Member
Re: Issue with reading Access database
As for have your connection string hardcoded, personal opinion, its a bad ideia, u should store somewhere else, and just load in to the program under application startup event for exemple.
-
Sep 27th, 2017, 02:04 PM
#8
Re: Issue with reading Access database
A couple of things,
Are you sure the Drive is installed on the target machine?
I think the program will be looking for this file on the local machine, not the network server. I don't know what type of network you have but when a program running on a workstation looks for a file on "C:" drive, it will probably be looking on the local machine.
-
Sep 27th, 2017, 02:07 PM
#9
Junior Member
Re: Issue with reading Access database
As Mike indicated, you should use either the Jet or ACE OLEDB Provider and not the MS Access ODBC driver, depending upon the version of the Microsoft Access database you are working with.
Code:
Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\...\Documents\My Database\Access\Northwind.accdb")
AccessConnection.Open()
Paul
~~~
Microsoft MVP (Visual Basic)
-
Sep 27th, 2017, 02:08 PM
#10
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by wes4dbt
A couple of things,
Are you sure the Drive is installed on the target machine?
I think the program will be looking for this file on the local machine, not the network server. I don't know what type of network you have but when a program running on a workstation looks for a file on "C:" drive, it will probably be looking on the local machine.
As I stated in post #4 ... my connection string in my code has the actual network path. I just used a dummy path in my example.
-
Sep 27th, 2017, 02:10 PM
#11
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by pclement
As Mike indicated, you should use either the Jet or ACE OLEDB Provider and not the MS Access ODBC driver, depending upon the version of the Microsoft Access database you are working with.
Any particular reason? Why does it work on my machine?
-
Sep 27th, 2017, 02:31 PM
#12
Re: Issue with reading Access database
Had a typo in my previous post. Meant to say,
Are you sure the ODBC Driver is installed on the target machine?
-
Sep 27th, 2017, 02:34 PM
#13
Junior Member
Re: Issue with reading Access database
Originally Posted by nbrege
Any particular reason? Why does it work on my machine?
If your app is configured to run 32-bit did you look in the 32-bit ODBC Data Source Administrator applet and verify that the driver is installed and exactly as specified in the connection string?
Paul
~~~
Microsoft MVP (Visual Basic)
-
Sep 27th, 2017, 02:35 PM
#14
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by wes4dbt
Had a typo in my previous post. Meant to say,
Are you sure the ODBC Driver is installed on the target machine?
-
Sep 27th, 2017, 02:35 PM
#15
Re: Issue with reading Access database
ODBC is the older technology (used in Windows 3 etc), and was superceeded by OLEDB in (I think) the late 1990's.
ODBC has various quirks that mean situations like this are not unusual (eg: if the client machines have a slightly different name for the driver, it wont be found - and you'll get this error), and there are several other situations that crop up too. Fixing them can be troublesome, and often takes a chunk of trial and error.
OLEDB is better designed, and is far less likely to have those kind of issues. It doesn't have any additional downsides (such as reduced speed), so there isn't really a reason to not switch to it.
edit: if that screenshot is from the target machine, it seems that the driver name is the same, in which case the cause is harder to track down... it has been a long time since I used ODBC, so unfortunately I'm not sure what to recommend.
-
Sep 27th, 2017, 02:40 PM
#16
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by si_the_geek
ODBC is the older technology (used in Windows 3 etc), and was superceeded by OLEDB in (I think) the late 1990's.
ODBC has various quirks that mean situations like this are not unusual (eg: if the client machines have a slightly different name for the driver, it wont be found - and you'll get this error), and there are several other situations that crop up too. Fixing them can be troublesome, and often takes a chunk of trial and error.
OLEDB is better designed, and is far less likely to have those kind of issues. It doesn't have any additional downsides (such as reduced speed), so there isn't really a reason to not switch to it.
OK, I will give that a shot. The only reason I didn't use OLEDB is because I tried using it on a previous project & had issues with being able write data to columns that had validation rules on them, even though the data I was writing complied with the rules. I didnt have that issue with ODBC. But on this app I dont need to write any data, so maybe OLE is the solution.
-
Sep 27th, 2017, 03:09 PM
#17
Junior Member
Re: Issue with reading Access database
You could try removing the extra set of braces around the driver name. Those should be unnecessary.
As I mentioned before, you really want to use the OLEDB provider. It's more reliable and has better support for Access specific functionality than the ODBC driver.
Paul
~~~
Microsoft MVP (Visual Basic)
-
Sep 27th, 2017, 03:18 PM
#18
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Well I just tried it using OLEDB and I still have problems. This is the connection string I'm using:
Code:
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\Engineering\Public Programs\PPI Material\Data\PPI_Material.accdb"
I get this error on another users PC:
Again, it works fine on my PC but not on others.
-
Sep 27th, 2017, 03:19 PM
#19
Hyperactive Member
Re: Issue with reading Access database
You may need to install this:
https://www.microsoft.com/en-us/down....aspx?id=13255
Microsoft Access Database Engine 2010 Redistributable
-
Sep 27th, 2017, 03:36 PM
#20
Junior Member
Re: Issue with reading Access database
It sounds to me like they either don't have Microsoft Office installed or they have a different platform installed than the app is running. For example, if your app is configured to run 32-bit then 64-bit Office (ACE OLEDB Provider) will not be detected by the app. If no version of Office has been installed then the corresponding version of the ACE OLEDB Provider will need to be installed (as Mike Storm has indicated).
Working with Visual Studio and Microsoft Access can be problematic with respect to configurations since there is no native .NET data access library for the database.
Paul ~~~~ Microsoft MVP (Visual Basic)
-
Sep 27th, 2017, 03:54 PM
#21
Thread Starter
Frenzied Member
Re: Issue with reading Access database
The PC I am testing on has both Access and Office installed. I am stumped at this point & ready to just use text files. I don't want to have users have to download & install anything on their PC's. Is there any data provider that is installed by default with Windows 7 that would allow me to connect to either an Access database or a Excel spreadsheet? Also, are there any third party data providers out there I could use? (preferably as a DLL that I reference in my project) Thanks...
-
Sep 27th, 2017, 03:59 PM
#22
Hyperactive Member
Re: Issue with reading Access database
I dont know what kind of data you storing in access, but if you can store it in text files, probably you can to in Xml, and that would provide you probably easyer ways to read and manipulate data, but returning to the point, you dont need office instale in the computer to use a access database, as for that OleDb driver, is redistributivel, so i belive you are alowd to ship it along your app, but that you will need to check on its documentation.
-
Sep 27th, 2017, 04:02 PM
#23
Junior Member
Re: Issue with reading Access database
Originally Posted by nbrege
The PC I am testing on has both Access and Office installed. I am stumped at this point & ready to just use text files. I don't want to have users have to download & install anything on their PC's. Is there any data provider that is installed by default with Windows 7 that would allow me to connect to either an Access database or a Excel spreadsheet? Also, are there any third party data providers out there I could use? (preferably as a DLL that I reference in my project) Thanks...
I generally recommend using SQLite, since you can deploy the database library, locally, with the app. You just need to get the correct assemblies. In addition, there are many database managers to choose from. I use this one.
Paul ~~~~ Microsoft MVP (Visual Basic)
-
Sep 27th, 2017, 04:04 PM
#24
Re: Issue with reading Access database
You can use JET, which has been pre-installed with Windows for many years.
It does mean however that you will need to switch from an .accdb file (which requires the ACE provider) to a .mdb file (which is supported by JET and ACE).
Using JET means that your application must be 32 bit (x86).
-
Sep 27th, 2017, 04:10 PM
#25
Re: Issue with reading Access database
You could use the Jet 4.0 provider (.MDB) database, it comes installed on all Windows 7 and newer machines, maybe even earlier but not sure.
Code:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|yourDatabase.mdb;Persist Security Info=True;User ID=admin;Jet OLEDB:Database Password=password1
EDIT - Si beat me to it.
-
Sep 27th, 2017, 04:11 PM
#26
Hyperactive Member
Re: Issue with reading Access database
Originally Posted by pclement
I generally recommend using SQLite, since you can deploy the database library, locally, with the app. You just need to get the correct assemblies. In addition, there are many database managers to choose from. I use this one.
Nice one, i was looking for something like that a while ago.
-
Sep 27th, 2017, 04:35 PM
#27
Thread Starter
Frenzied Member
Re: Issue with reading Access database
I will try the Jet provider tomorrow morning. If that doesn't work then I think I will give this a try...
-
Sep 28th, 2017, 01:54 PM
#28
Thread Starter
Frenzied Member
Re: Issue with reading Access database
OK, I just got a chance to try the Jet provider and now I'm getting this error on my dev machine:
"The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine"
Any ideas?
-
Sep 28th, 2017, 02:58 PM
#29
Junior Member
Re: Issue with reading Access database
Sounds like your app is running 64-bit.
Paul ~~~~ Microsoft MVP (Visual Basic)
-
Sep 28th, 2017, 03:27 PM
#30
Thread Starter
Frenzied Member
Re: Issue with reading Access database
Originally Posted by pclement
Sounds like your app is running 64-bit.
Ahhh, yes you are correct. I temporarily switched to compile to x64. When I switched to x86 it ran fine on the target PC. So it looks like I have a path forward now. Thanks for everybodies help...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|