How to connect linked ODBC tables from VBA? 1

[joebickley]

Hi

I have a databse with a SQL server back end. It is under developemnt and i am getting bored of having to detatch and reatatch the linked tables everytime i make a change. Does anyone know the VBA code for linking a table so i can just set up a button

PS the linked table manager will not work for some reason i allready tried.

JB Office automation specialist
Land Rover UK

 

[rbowes]

Here's how I do it:
1: Create a local table called tblLinkedTables which has the table name (with SQL Server they usually start like dbo_...)field and the SourceName (Which is the table name without the dbo_). Fill this with the names of the tables that you want to link from SQL Server. It makes additions and subtractions very easy.

2: Set a Constant in a standard module that holds the ODBC path. (Hint: to get the full path with password, create a Pass-Through query and copy the ODBC Connect Str under query properties.)

Public Function LinktoSQL() As Boolean
Dim db As DAO.Database
Dim i As Integer
Dim tdfNew As DAO.TableDef
Dim rec As DAO.Recordset
Dim tdfLinked As DAO.TableDef
Dim strtablename As String

On Error GoTo LinktoSQL_Err

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblLinkedTables&quot
rec.MoveLast
rec.MoveFirst

For i = 1 To rec.RecordCount
strtablename = rec.Fields(0).Value
Set tdfNew = db.CreateTableDef(strtablename)
tdfNew.SourceTableName = rec.Fields(1).Value
tdfNew.Connect = cODBCPATH 'Constant ODBC Conn. string
db.TableDefs.Append tdfNew
rec.MoveNext
Next i
LinktoSQL_Exit:
set rec = Nothing
Exit Function
LinktoSQL_Err:
Select Case Err.Number
Case 3010 'In case the linked table is there
db.TableDefs.Delete strtablename
db.TableDefs.Append tdfNew
Resume Next
Case Else
MsgBox Err.Number & Err.Description
LinktoSQL = False
End Select
End Function

This routine has worked well with the app I have distributed. One note: The ODBC Connection string will not work for ADO objects.

Hope that helps.

Bob s-)

 

[joebickley]

Great

Nice one mate had a few problems with it but it was my access not ur code. U have to turn the ADO object library on or it does not work. It confused the heck out of me but it helps with some things at work.

Do you know if it is best to turn all of the object librarys on?

Thanks Mate Office automation specialist
Land Rover UK

 

[rbowes]

I only reference the ones that I am using. I'm using A2000 which has a default library set to ADO 2.x. In the case of the linking, the linked tables are part of the tabledefs and are accessobjects. Since, DAO is native to Access, I set the reference to the DAO 3.6 library. Oh, and the tdfLinked declaration should be deleted (its not used; and I need to do a little cleaning!). For Automation, I set references to Excel and Outlook and Word. Beyond that, the references I set are for the use of ActiveX controls like Listviews, Microsoft Forms 2.0 and Window Common Controls.

I think the problem with setting references to all of the libraries is the possibility of getting a missing reference when you install on a clean machine.

Bob