Microsoft Access doesn't find ODBC file.

Question

I'm trying to create a connection to Northwind database in sql server from a function in Microsoft Access 2019.

I have a saved ODBC connection called Northwind.

When I use the following code:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = OpenDatabase("Northwind")
    Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)

I receive an error saying Northwind couldn't be located.
Images are attached.

Any kind of advice is much appreciated.

Answer 1

If you really want to do this with DAO (as you've described), then you would specify the connection string referring to the DSN as the fourth argument of OpenDatabase.  Like this:

    Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

This thread sounds familiar. Is it related to this one? What I could suggest at this point is try to create a new User DSN using a different name and then also try using the code on a new blank database, just to rule out any "corruption" with the original objects/files you were using. Just my 2 cents...

Answer 3

Hello, 

There are two separate pull of ODBC drivers - for x32 and fro x64 - and two different ODBC administrators.

Most likely you have x64 version of MS ACCESS 2019 and it look in the x64 pull where are no Nortwind ODBC reference. 

Solution: Find another - ODBC administrator (x64) - and create new reference.

---

Sincerely, Highly skilled coding monkey.

Answer 4

>Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

There are no difference how to access to ODBC driver.

---

Sincerely, Highly skilled coding monkey.

Answer 5

>Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

There are no difference how to access to ODBC driver.

I'm not sure what you're saying, Andrey, but for me, 

    Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

works, while

    Set db = DBEngine.OpenDatabase("Northwind")

does not.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

Both supposed to load and configure ODBC-driver.

Problem is which one will be loaded. Most likely - x64, but definition provided for x32 only. 

Optional - User DSN, System DNS, File DSN. 

Can't test this - didn't have or use Access for very long time.

---

Sincerely, Highly skilled coding monkey.

Answer 7

Both supposed to load and configure ODBC-driver.

Problem is which one will be loaded. Most likely - x64, but definition provided for x32 only. 

There definitely could be a problem with a DSN that is defined for the wrong "bit-ness" -- 32-bit DSN when the Access version is 64-bit, or 64-bit DSN when Access is 32-bit.  However, I find that even when there is not, the DSN must be given in the "connect" argument to OpenDatabase, not in the "dbname" argument.  

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 8

If you really want to do this with DAO (as you've described), then you would specify the connection string referring to the DSN as the fourth argument of OpenDatabase.  Like this:

    Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Thanks for your advice

I'm aware of this or other possible solutions. My question was why Opendatabase("Northwind") doesn't work.
According to Microsoft documentation it should.

Answer 9

This thread sounds familiar. Is it related to this one? What I could suggest at this point is try to create a new User DSN using a different name and then also try using the code on a new blank database, just to rule out any "corruption" with the original objects/files you were using. Just my 2 cents...

Yes that's the one.

I'm still searching for an answer why it doesn't work.

Answer 10

This thread sounds familiar. Is it related to this one? What I could suggest at this point is try to create a new User DSN using a different name and then also try using the code on a new blank database, just to rule out any "corruption" with the original objects/files you were using. Just my 2 cents...

Yes that's the one.

I'm still searching for an answer why it doesn't work.

The way I look at it, it works somewhere else but not with the one you're trying. So, we'll need to find out what's different with your setup as compared to those where this works. That's why I recommended you trying it out with brand new objects (DSN and Access file), just to eliminate your current objects as being corrupt. You could even go as far as trying it out on new objects on a different computer, for the same reason. If you find out it still doesn't work, then we know it's not because of how you did it (when you were following the YouTube instructions). Otherwise, if you managed to make it work using new objects or different computer, then we can say there's something definitely wrong with the original objects you were using (DSN and Access file). Just my 2 cents...

Answer 11

Hello, 

There are two separate pull of ODBC drivers - for x32 and fro x64 - and two different ODBC administrators.

Most likely you have x64 version of MS ACCESS 2019 and it look in the x64 pull where are no Nortwind ODBC reference. 

Solution: Find another - ODBC administrator (x64) - and create new reference.

---

Sincerely, Highly skilled coding monkey.

Thanks for your input.

Yes I have x64 version of Access 2019. As you can see in above screenshot, the platform for Northwind is 32/64 bit. This connection is created by control panel-Administrative Tools-ODBC Data Sources(64-bit) wizard and I imagined it should be under correct admin.

I've gone through this wizard more than 20 times and created different connection files under different names, but still the error is the same.

Answer 12

>Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

There are no difference how to access to ODBC driver.

I'm not sure what you're saying, Andrey, but for me, 

    Set db = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=Northwind")

works, while

    Set db = DBEngine.OpenDatabase("Northwind")

does not.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

For me it's the same. 

I'm searching why OpenDatabase doesn't respond. According to Microsoft Documents on OpenDatase method and this video https://youtu.be/soSKpnM4wcg?t=6m42s it should.

Answer 13

Both supposed to load and configure ODBC-driver.

Problem is which one will be loaded. Most likely - x64, but definition provided for x32 only. 

Optional - User DSN, System DNS, File DSN. 

Can't test this - didn't have or use Access for very long time.

---

Sincerely, Highly skilled coding monkey.

I made a System DSN. Still the same error.

Answer 14

Both supposed to load and configure ODBC-driver.

Problem is which one will be loaded. Most likely - x64, but definition provided for x32 only. 

There definitely could be a problem with a DSN that is defined for the wrong "bit-ness" -- 32-bit DSN when the Access version is 64-bit, or 64-bit DSN when Access is 32-bit.  However, I find that even when there is not, the DSN must be given in the "connect" argument to OpenDatabase, not in the "dbname" argument.  

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

This is the documetation for DAO Open databse.

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-opendatabase-method-dao

Name is required and the rest optional. db.OpenRecordset(myConnection_Name) should be enough. I've seen examples on youtube that this method works. But for me it brings up the error.

Answer 15

My question was why Opendatabase("Northwind") doesn't work.

According to Microsoft documentation it should.

It should according to the documentation for Access 2013 -- I can't immediately find a later version of the DAO documentation than that.  However, documentation for Access 2003 does *not* say that it should work; that documentation says you have to do it the way I showed. 

Now, Access 2003 was a long time ago, and it could be that when the Access product team split off the Jet database engine as ACE for Access 2007, they enhanced it to allow a DSN to be specified in the "dbname" argument.  Maybe that added functionality was broken by a later version and nobody noticed, or maybe they intended to add that functionality but never did.  I'd love to hear from someone who has made it work the way the 2013 documentation says.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 16

I'd love to hear from someone who has made it work the way the 2013 documentation says.

And I see from the link in another of your posts that it did work for someone else, using Access 2013.  Are you using Access 2013, or a more recent version?  Maybe this is a recent regression.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 17

My question was why Opendatabase("Northwind") doesn't work.

According to Microsoft documentation it should.

It should according to the documentation for Access 2013 -- I can't immediately find a later version of the DAO documentation than that.  However, documentation for Access 2003 does *not* say that it should work; that documentation says you have to do it the way I showed. 

Now, Access 2003 was a long time ago, and it could be that when the Access product team split off the Jet database engine as ACE for Access 2007, they enhanced it to allow a DSN to be specified in the "dbname" argument.  Maybe that added functionality was broken by a later version and nobody noticed, or maybe they intended to add that functionality but never did.  I'd love to hear from someone who has made it work the way the 2013 documentation says.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

If this method doesn't work in new versions of Access, that's OK. I never intended to use it. I was studying different methods than what I normally use in my databases and came across several videos in youtube (I posted the link above). It always bothers me to see something works for others and not for me. I just was looking for an explanation. If it's normal that Access 2019 brings up this error, I'm OK.

Million thanks for your time and advice.

Answer 18

Read this about DSN :

https://support.microsoft.com/en-ca/help/942976/odbc-administrator-tool-displays-both-the-32-bit-and-the-64-bit-user-d

---

Sincerely, Highly skilled coding monkey.