VS 2017 Issue with reading Access database-VBForums
Results 1 to 30 of 30

Thread: Issue with reading Access database

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    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...

  2. #2
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Issue with reading Access database

    I belive you need to override "dbConnStr " on application startup to the network folder

  3. #3
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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?

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by Mike Storm View Post
    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...

  5. #5
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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.

  6. #6
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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.

  7. #7
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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.

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,352

    Re: Issue with reading Access database

    A couple of things,

    Are you sure the Drive is installed on the target machine?

    c:\tmp\MyDbFile.accdb
    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.

  9. #9
    Junior Member
    Join Date
    May 2012
    Posts
    26

    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)

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by wes4dbt View Post
    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.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by pclement View Post
    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?

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,352

    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?

  13. #13
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Issue with reading Access database

    Quote Originally Posted by nbrege View Post
    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)

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by wes4dbt View Post
    Had a typo in my previous post. Meant to say,

    Are you sure the ODBC Driver is installed on the target machine?
    Name:  Capture.JPG
Views: 52
Size:  55.9 KB

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,254

    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.

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by si_the_geek View Post
    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.

  17. #17
    Junior Member
    Join Date
    May 2012
    Posts
    26

    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)

  18. #18

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    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:

    Name:  Capture.JPG
Views: 47
Size:  53.7 KB

    Again, it works fine on my PC but not on others.

  19. #19
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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

  20. #20
    Junior Member
    Join Date
    May 2012
    Posts
    26

    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)

  21. #21

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    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...

  22. #22
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    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.

  23. #23
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Issue with reading Access database

    Quote Originally Posted by nbrege View Post
    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)

  24. #24
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,254

    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).

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,352

    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.

  26. #26
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: Issue with reading Access database

    Quote Originally Posted by pclement View Post
    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.

  27. #27

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    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...

  28. #28

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    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?

  29. #29
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Issue with reading Access database

    Sounds like your app is running 64-bit.
    Paul ~~~~ Microsoft MVP (Visual Basic)

  30. #30

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,834

    Re: Issue with reading Access database

    Quote Originally Posted by pclement View Post
    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.