The AutoExec macro is of course optional. You could just maintain the links maually by running the code from the debug window as needed.

Hi Bob,

Thanks for all of this - I spotted it yesterday, but cannot test it because I cannot dial in to work server from home. Frustrating or what?!!!

I had actually noticed that the links were deleted and wondered if this is necessary. As the database stands, the links have always been in place and it is safe to leave them in place. Having said that, I am sure you know what you are doing, but am just curious.

All the best,


Roly

Yikes Roly! Good to know at least one of us is ;)!

Nope, you don't need to delete the links. I was just thinking it would be easier to refresh them at startup. Access won't auomatically reflect any underlying table structure changes you make on SQL Server, so if you change table structures or add new user tables, you'll have to manually refresh the existing links or create new ones. But then you probably already knew that. Also, there may be some user tables on SQL Server that you don't want to establish links for.

To establish the links manually on a table-by-table basis, you could just use Warren's AttachTable_ClientServer function.

Just for the heck of it though, here's a bare bones Linking routine that includes all you need to establish a new link, and then leave it alone forever. Just supply the name of the remote table, and the name you want to use for the local table. I'm just connecting to the Sql Server Northwind db on my local system.

Sub CreateLinkedSQLServerTable(strLocalTableName As String, strRemoteTableName)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog

    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = New ADOX.Table

    tbl.Name = strLocalTableName
    Set tbl.ParentCatalog = cat

    tbl.Properties("Jet OLEDB:Link Provider String") = _
                       "ODBC" & _
                       ";Driver=SQL Server" & _
                       ";Server=(local)" & _
                       ";Database=Northwind" & _
                       ";UID=sa" & _
                       ";pwd=myPassword"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strRemoteTableName
    tbl.Properties("Jet OLEDB:Create Link") = True

    cat.Tables.Append tbl
    Set cat = Nothing

    Application.RefreshDatabaseWindow

End Sub

Call the sub with something like:

CreateLinkedSQLServerTable "Customers", "dbo.Customers"

Don't know if you've run code from the VBA Editor before but its really easy. Just create a new standard module, paste the above routine into it, open the Immediate Window by pressing Ctrl-G, type the procedure call in the immediate Window, place your cursor at the end of the procedure call, and hit enter.

HTH,

Bob

You will, of course, have to delete you're existing DSN links before establishing the DSN-less links....

Hi Bob,

I have tried running the first function, "Function RetrieveSQLServerUserTables()", but the program stops responding and crashes (hourglass mouse and cannot use that Access application). I have tried letting it run for ten minutes, but still the same. I tried changing the DSN slightly and when I ran the code it did not recognise the server name.

I am unsure of how to get it working - I changed the details in six places as per your code (well pointed out!) - I hope this is correct.

Any ideas?

All the best,


Roly

Not really sure Roly.

The "Server=" parameter should just be the name of a server running SQL Server on your network.

Bob

Dear Bob,

I have been playing with the code today.

When I delete the second half of the code from Function RetrieveSQLServerUserTables() after password line, the code ran properly, but has not allowed me to access the tables, changing code in a variety of different ways. It definitely recognises the server, database, username and password, because when I changed them, a message box appears stating that it does not recognise the name.

Is there any simpler code that might work on its own?

I really appreciate your help in all of this.

All the best,


Roly

Hi Roly,

Have you tried to bare bones routine I posted that just accepts your local and remote table names as arguments? I think that's the simplest version possible. Or try Warren's procedure (you'll need to set a reference to DAO). The pared down code I posted was:

Sub CreateLinkedSQLServerTable(strLocalTableName As String, strRemoteTableName)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog

    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = New ADOX.Table

    tbl.Name = strLocalTableName
    Set tbl.ParentCatalog = cat

    tbl.Properties("Jet OLEDB:Link Provider String") = _
                       "ODBC" & _
                       ";Driver=SQL Server" & _
                       ";Server=yourServer" & _
                       ";Database=yourDatabase" & _
                       ";UID=yourUserID" & _
                       ";pwd=yourPassword"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strRemoteTableName
    tbl.Properties("Jet OLEDB:Create Link") = True

    cat.Tables.Append tbl
    Set cat = Nothing

    Application.RefreshDatabaseWindow

End Sub

'Call CreateLinkedSQLServerTable "Customers", "dbo.Customers"

Seems that you have the correct connection properties set, which is half the battle. I havn't used linked SQL Server tables in a production application before (I work with ADO or DAO recordsets), so any number of things might be happening that I'm not able to anticipate. But I'd like to see you get it up and running, and learn as we go as well.

Bob

Hi Bob,

Still no luck - the code runs OK but you still have to enter the password.

Not sure what to try next.

Thanks for all of your help on this one.

All the best,


Roly

Sorry Roly, can't think of anything else to suggest. Have you run this stuff past your network admin folks? Maybe its some kind of permissions problem??? :(

Bob