linking tables from code and locations

[DawnTreader]

Hello

i have a quick question about linking tables from code. i know it can be
done, but the question i have is can i get it to look at the type of link and
choose to not refresh the link or change the source?

for example, i have an app that i develope on my local machine that i want
to "publish". the frontend has links to an access database and to an odbc
connection. when i publish i manually go in and tell it which links i want to
change by using the linked table manager. easy process, but if there was a
way to create a bit of code that i can put behind a button on a developer
form that i can tell it to only change the connection on the access links, so
much the better. it would speed up my development time immensely.

thanks for any and all suggestions.

 

[Jim Burke in Novi]

I don't know how to distinguish the Access tables from the ODBC tables, but
here's code that I use for re-linking my tables (from Access 2000):

Public Function fRefreshLinks() As Boolean

'--------------------------------------------------------------------------------------------
' Re-link all tables.
'--------------------------------------------------------------------------------------------

Dim objCat As New ADOX.Catalog
Dim objTbl As ADOX.Table

objCat.ActiveConnection = CurrentProject.Connection
For Each objTbl In objCat.Tables
If objTbl.Type = "LINK" Then
If Not LinkTable(objTbl, dataDBName) Then
fRefreshLinks = False
GoTo ExitHandler
End If
End If
Next
fRefreshLinks = True

ExitHandler:
Set objCat = Nothing
Set objTbl = Nothing

End Function

Public Function LinkTable(ByRef objTbl As ADOX.Table, ByVal DBName As
String) As Boolean

On Error GoTo ErrorHandler

LinkTable = True
objTbl.Properties("Jet OLEDB:Link Datasource") = DBName
Exit Function

ErrorHandler:
LinkTable = False
' generate error message here
Resume Next

End Function

The statement 'If objTbl.Type = "LINK" Then' determines if it's a linked
table. If there in't a property to tell you the source of the table (Access
or 'other'), do you have a naming convention where the tables are named
diffferently? You need to add a reference in order to use ADOX for the
catalog and the tables. I think it's MIcrosoft ADO Extension 2._ for DDL and
Security, or else Microsoft ActiveX Data Objects. The 'dataDBName' field is
one I create that has the name of my backend database, so you'd need to
change that.

 

[Douglas J. Steele]

The connection string (the TableDef object's Connect property) for ODBC
connections will have ODBC; at the front of it. The connection string for
linked Access tables will have ;DATABASE= at the front of it. That means
that you can use code like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If Len(tdfCurr.Connect) > 0 Then
If Left(tdfCurr.Connect, 5) = "ODBC;" Then
' It's linked using ODBC
ElseIf Leff(tdfCurr.Connect, 10) = ";DATABASE=" Then
' It's a linked Access table
End If
End If
Next tdfCurr

 

[DawnTreader]

Nice... !

thanks a lot. this will make things a lot easier. when i get a chance to
implement code...

 

[DawnTreader]

Hello again guys

i have another question relating to this situation.

how do i know whether to use ADO or DAO?

i have no idea which i am using, although i have some code that uses ADODB
in it.

i dont even understand the difference other than it is different connections
or data models.

the code that uses the ADODB is used to add records to a few different
tables in the database. this code is in a form on the front end.

so my question is do i use DAO or ADO for this relinking of the tables and
how do i tell which and why?

 

[Douglas J. Steele]

Since you're strictly working with a Jet database (i.e. an MDB file) or an
ACE database (an ACCDB file in Access 2007), my advice is to use DAO. DAO
was developed specifically to work with Jet, and so avoids some of the
levels of abstraction included in ADO (which is a more generic approach)

 

[DawnTreader]

Hello Doug.

big thanks. this code i have now is working well. no more wasting time
relinking manually.