Linking SQL Server tables with VBA (1 Viewer)

[Hello1]

I saw somewhere on the internet that I could link the SQL Server tables to my .accdb with VBA, without having to use the Linked table manager.
Sometimes I get the ODBC call failed when trying to link tables with the Linked table manager and then I have to add every table again to the .accdb, quite annoying and time consuming.
Does someone have a thread here on the forum maybe with details how to and what are the advantages?

Thanks

 

[pbaldy]

Here's a commonly posted link:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

I use a slightly different method with a local table.

 

[Hello1]

Im trying to implement this but the problem I face is having tables linked to 2 databases on the SQL Server and not just one database.
After the code goes through all ODBC tables it picks those from the other database which I dont need and that causes an error because it cant find the table.
Any suggestion how to make this part look for tables only in Database1 and not in Database2 also:

For Each tdfCurrent In dbCurrent.TableDefs
    If Len(tdfCurrent.Connect) > 0 Then
      If UCase$(Left$(tdfCurrent.Connect, 5)) = "ODBC;" Then
        ReDim Preserve typNewTables(0 To intToChange)
        typNewTables(intToChange).Attributes = tdfCurrent.Attributes
        typNewTables(intToChange).TableName = tdfCurrent.Name
        typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
        typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
        typNewTables(intToChange).Description = Null
        typNewTables(intToChange).Description = tdfCurrent.Properties("Description")
        intToChange = intToChange + 1
      End If
    End If
  Next

 

[isladogs]

Set the value of dBCurrent before that code.

You may also find the attached useful. It shows the forms and code I use for managing DSN less connections to one or more Access or SQL Server backends. As I've not included any backend tables, the code won't run as written but it may help you move forward

Hope that helps

 

[Hello1]

I went with with this and seems to work for now:

If UCase$(Left$(tdfCurrent.Connect, 5)) = "ODBC;" And InStr(1, tdfCurrent.Connect, DatabaseName, vbTextCompare) <> 0 Then

However, I will definitely check your example

Set the value of dBCurrent before that code.

How exactly do I do that?

 

[pbaldy]

I adapted this:

https://support.microsoft.com/en-us/kb/892490

to use a local table with fields for table name, database name, and server name. When the app starts I open a recordset on that table and loop through it, passing each required table to a function that links it.

 

[NauticalGent]

When the app starts I open a recordset on that table and loop through it, passing each required table to a function that links it.

I use something very similar to this. What I like about it is that when I make changes to the BE, I do not have to relink the "master" FE and re-deploy it to the masses. Adding new tables with this method is a breeze as well...

 

[isladogs]

If you look at my code database in my previous reply, my approach is very similar except I have two local tables.
The first table lists the names and aliases of all linked tables and the database each comes from.
The second table gives the connection info for each of the linked databases.

I've used DSN less connections for years precisely because it is so simple to set up and maintain. Also because there is no need to setup each workstation individually.

 

[pbaldy]

I use something very similar to this. What I like about it is that when I make changes to the BE, I do not have to relink the "master" FE and re-deploy it to the masses. Adding new tables with this method is a breeze as well...

It also makes it easy to switch between test and production servers/databases.

 

[theDBguy]

I adapted this:

https://support.microsoft.com/en-us/kb/892490

to use a local table with fields for table name, database name, and server name. When the app starts I open a recordset on that table and loop through it, passing each required table to a function that links it.

Hi Paul. Just curious, in the link you posted, do you use Method 1 or Method 2?

 

[isladogs]

I know you were asking Paul … but for info I use method 1.
AFAIK I've never seen method 2 being used.

 

[pbaldy]

Basically 1, but instead of looping the TableDefs I loop my local table and delete/link each one. Probably what you're after is that I use CreateTableDef not RegisterDatabase. If there's a debate, I won't take sides. I'd probably be using RegisterDatabase if it had been listed first.

 

[theDBguy]

Basically 1, but instead of looping the TableDefs I loop my local table and delete/link each one. Probably what you're after is that I use CreateTableDef not RegisterDatabase. If there's a debate, I won't take sides. I'd probably be using RegisterDatabase if it had been listed first.

Hi Paul. Thanks! Actually, the "debate" I was thinking of is between deleting the tables versus simply refreshing the links (connect string). I have been refreshing the links and wondering if I should be deleting instead.

 

[pbaldy]

I won't take a position on that either. Sadly I'm somebody who figures out how to make things work but doesn't always understand what's happening under the hood/bonnet.

 

[theDBguy]

I won't take a position on that either. Sadly I'm somebody who figures out how to make things work but doesn't always understand what's happening under the hood/bonnet.

Okay, thanks. Maybe it's a question for Colin then. Hey Colin, is there a difference? How about speed?

 

[isladogs]

Sorry I can't offer much guidance either

I always delete all existing connections then loop through recreating the links
Even for 300+ linked tables on a network it typically takes no more than 30-45 seconds in total

As that approach works reliably and is very fast, I've stuck with that for the past 15 years

 

[theDBguy]

Sorry I can't offer much guidance either

I always delete all existing connections then loop through recreating the links
Even for 300+ linked tables on a network it typically takes no more than 30-45 seconds in total

As that approach works reliably and is very fast, I've stuck with that for the past 15 years

Okay, thanks! I'll have to give it a try. Cheers!

 

[NauticalGent]

Here is the method I adapted. My app only has about 50+ tables and it only takes about 3 seconds for this whole routine to run:

http://etutorials.org/Microsoft+Pro...ynamically+Link+SQL+Server+Tables+at+Runtime/

 

[theDBguy]

Here is the method I adapted. My app only has about 50+ tables and it only takes about 3 seconds for this whole routine to run:

http://etutorials.org/Microsoft+Pro...ynamically+Link+SQL+Server+Tables+at+Runtime/

Hi John. Thanks for the link. That looks similar to Method 1 from Paul's link. Cheers!

 

[theDBguy]

Out of curiosity, I checked which method Doug was using in his famous article. It looks like I'm the only one doing it the way I'm doing, so maybe I am doing it wrong. I may have to make some changes now.