How to add a SQL connection (without linked tables)? (1 Viewer)

deletedT · Feb 23, 2019

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:

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.

Am I missing anything?
Any kind of advice is much appreciated.

theDBguy · Feb 23, 2019

Hi. If you don’t want to link to the table, you’ll need to use ADO with a connection string.

deletedT · Feb 23, 2019

theDBguy said:
Hi. If you don’t want to link to the table, you’ll need to use ADO with a connection string.

Doesn't OpenRecordset(connection name) open a connection in DAO?

I'm sure I've seen it somewhere ......

PS: I think I found it.
https://youtu.be/soSKpnM4wcg?t=6m42s

isladogs · Feb 23, 2019

Just wondering what your reasons are for doing this.
If you are trying to avoid users being able to view connection strings then try adapting my approach in this demo:
https://www.access-programmers.co.uk/forums/showpost.php?p=1612513&postcount=7

That's currently for an Access BE but the idea should work for a SQL BE as well

theDBguy · Feb 23, 2019

Tera said:
Doesn't OpenRecordset(connection name) open a connection in DAO?

I'm sure I've seen it somewhere ......

PS: I think I found it.
https://youtu.be/soSKpnM4wcg?t=6m42s

Hi. You are absolutely correct. However, as I was saying, you'll need to use ADO - not DAO, if you don't want to link to the table. In ADO, you will use the .Open method - not the .OpenRecordset method.
Take a look at this link. What I was referring to is the ActiveConnection argument, which could take a Connection String. If you don't know what to use, you can take a look at the Connection String website.

pbaldy · Feb 23, 2019

I'm on a mobile right now, but I'm fairly sure you can use DAO, you just need proper arguments in OpenDatabase. I'll test when I get on a computer later.

theDBguy · Feb 23, 2019

pbaldy said:
I'm on a mobile right now, but I'm fairly sure you can use DAO, you just need proper arguments in OpenDatabase. I'll test when I get on a computer later.

Thanks, Paul. That would be nice to know. Cheers!

Edit: Paul must be talking about the Connect argument of the OpenDatabase method.
Edit: The author did use the Connect argument at this point in the video.

deletedT · Feb 23, 2019

isladogs said:
Just wondering what your reasons are for doing this.

Nothing special.
Just wondering if Steve Bishop can do it, why I can not.

In the video he can connect to the dstabase but I'm receiving this error. Why?

deletedT · Feb 23, 2019

pbaldy said:
I'm on a mobile right now, but I'm fairly sure you can use DAO, you just need proper arguments in OpenDatabase. I'll test when I get on a computer later.

I appreciate it.
And will be waiting to see what will the result be.

Sent from my SO-04J using Tapatalk

theDBguy · Feb 23, 2019

Tera said:
Nothing special.
Just wondering if Steve Bishop can do it, why I can not.

In the video he can connect to the dstabase but I'm receiving this error. Why?

Hi. I watched the video from the beginning up to the point you showed me earlier, and I think the difference may be is he (Bishop) created a user DSN to connect to SQL Server, so he can use the DSN Name (Northwind) in the OpenDatabase method. So, the question is, did you also create a user DSN called Northwind on your machine? At this point in the video, he explains why he didn't get an error.

PS. I'll continue to watch the rest of the video because it started with linked tables and he's supposed to be showing how to get rid of them.

Gasman · Feb 23, 2019

Tera said:
Nothing special.
Just wondering if Steve Bishop can do it, why I can not.

In the video he can connect to the dstabase but I'm receiving this error. Why?

He set up a new DSN connection. Did you do that as well?

deletedT · Feb 23, 2019

Gasman said:
He set up a new DSN connection. Did you do that as well?

Yes.

But does it make any difference it's new or not?

Sent from my SO-04J using Tapatalk

pbaldy · Feb 23, 2019

In a test db this works:

Code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = OpenDatabase("", False, False, "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName;DATABASE=CabTS;Trusted_Connection=Yes")
  
  strSQL = "SELECT * FROM tblMedallionTypes"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

deletedT · Feb 23, 2019

theDBguy said:
Hi. I watched the video from the beginning up to the point you showed me earlier, and I think the difference may be is he (Bishop) created a user DSN to connect to SQL Server, so he can use the DSN Name (Northwind) in the OpenDatabase method. So, the question is, did you also create a user DSN called Northwind on your machine?

As you can see in the screen shot in my first post above, yes I have a DSN name just like him.

theDBguy · Feb 23, 2019

Tera said:
As you can see in the screen shot in my first post above, yes I have a DSN name just like him.

Ah, sorry, I didn't look at your attachments earlier. No clue then...

deletedT · Feb 23, 2019

pbaldy said:

In a test db this works:

Code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = OpenDatabase("", False, False, "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=ServerName;DATABASE=CabTS;Trusted_Connection=Yes")
  
  strSQL = "SELECT * FROM tblMedallionTypes"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Million thanks for your efforts. But I'm looking for a reason why the method in the video doesn't work for me.

Gasman · Feb 23, 2019

Tera said:
Yes.

But does it make any difference it's new or not?

Sent from my SO-04J using Tapatalk

No idea, never done it, but I was reading that as naming the connection as Northwind, then using that to connect?. Everything else was stored in that connection.

pbaldy · Feb 23, 2019

Tera said:
Million thanks for your efforts. But I'm looking for a reason why the method in the video doesn't work for me.

Sorry for going offtrack.

deletedT · Feb 23, 2019

pbaldy said:
Sorry for going offtrack.

No,that was a great help and very kind of you to put your time and effort to find some equivalent way to do the same task.

Unfortunately I'm not in front of my PC at the moment (weekend holiday) to test it. But will check it out monday morning as soon as I'm back to work.

Thank you.

deletedT · Mar 1, 2019

According to Dirk Goldgar in this post, this method doesn't work for new versions of Microsoft Access. It seems 2013 version is the last version for this method.

I was advised to use other methods (As most of you told me the same here).

Million thanks for your time and sharing your experience.

How to add a SQL connection (without linked tables)? (1 Viewer)

Guest

Attachments

I’m here to help

Guest

MVP / VIP

I’m here to help

Wino Moderator

I’m here to help

Guest

Guest

I’m here to help

Enthusiastic Amateur

Guest

Wino Moderator

Guest

I’m here to help

Guest

Enthusiastic Amateur

Wino Moderator

Guest

Guest

Similar threads

Users who are viewing this thread