How to run code to relink tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to run code to relink tables

Author

Topic

LaurieW
Starting Member

4 Posts

Posted - 2004-10-25 : 13:10:44

I am working on my first bound application between SQL Server and Access 2000. I am using a manual to try to set this up. The manual has given the following code to link one table/relink all tables. I assume I should run the relink all tables code every time a user opens the database ... but how do I get it to run? I've tried several different variations (macro using RunCode; on a form in the Open event) and always come up with errors. What am I missing here? It must be something simple ... thanks, Laurie

Here's the code for the first module called "LinkSingleTable":
Public Function LinkTableDAO( _
strLinkName As String, _
strDBName As String, _
strTableName As String, _
strDSNname As String) As Boolean

'Links or re-links a single table.
'Returns True or False based on Err value.

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error Resume Next
Set db = CurrentDb
' if the link already exists, delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
'Ignore error and reset
Err.Number = 0
End If

'Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)

'Set the Connect and SourceTableName properties to establish the link
tdf.Connect = "ODBC;Database=" & strDBName & ";DSN = " & strDSNname _
& ";Integrated Security=True"
tdf.SourceTableName = strTableName

'Append to the database's TableDefs collection
db.TableDefs.Append tdf

LinkTableDAO = (Err = 0)
End Function
* * * * *
Here is the code for the second module called "LinkAllTables":
Function RelinkAllTables( _
strSQLDB As String, _
strDSN As String) As Boolean

'Relinks existing ODBc linked tables

Dim tdf As TableDef
Dim fLink As Boolean

On Error GoTo HandleErr
RelinkAllTables = False
For Each tdf In CurrentDb.TableDefs
With tdf
'Only process linked ODBC tables
If .Attributes = dbAttachedODBC Then
fLink = LinkTableDAO( _
strLinkName:=.Name, _
strDBName:=strSQLDB, _
strTableName:=.SourceTableName, _
strDSNname:=strDSN)
'If there's a problem with one table, don't bother processing the rest.
If Not fLink Then GoTo ExitHere
End If
End With
Next tdf
RelinkAllTables = fLink

ExitHere:
Exit Function

HandleErr:
RelinkAllTables = False
MsgBox _
Prompt:=Err & ": " & Err.Description, Title:="Error in RelinkAllTables"
Resume ExitHere

End Function

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-25 : 13:54:34

Why do you want to run this every time the user opens the database? Are you familiar with the concept of linked tables? The data is dynamically linked -- each time you open or access the linked table, data is retrieved real-time from the server. It is not like you need to re-import the data each time.

If you feel you need to always re-link in case the table structures you are linking to change, then you have a bigger problem on your hands. YOur access app will not work well with an ever-changing SQL Server structure. How will reports, forms, macros, etc, adjust themselves when your tables and views have column names that change, or are added, or are deleted?

- Jeff

LaurieW
Starting Member

4 Posts

Posted - 2004-10-25 : 15:05:49

The manual/learning tools I am using say this "you will most likely need to relink tables using VBA so that your users do not have to manually reconnect linked tables should they ever need to reconnect."
Going from that statement, it seemed to me that what they were saying was to go ahead and relink the tables so users don't run into problems with a missing table.

Once again, this is my first SQL/Access venture so I can only go from what my resources tell me.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-25 : 15:11:14

You could try using a "Sql Server Project" in Access2000.
This will create an edit: ~~.adb~~ .adp file and not a .mdb file.
This way all the objects in SQL Server will be available in the Access Project, and you will not need to link the tables at all.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

LaurieW
Starting Member

4 Posts

Posted - 2004-10-25 : 15:20:36

Thanks for your comment ... I wanted to try the bound application first, as it will let me use much of what I already know about Access and VBA.

L.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-25 : 15:27:49

your "learning tool" that you are reading makes no sense, though.

consider:

1) you develop an Access application, with forms, queries, reports, and all tables are linked to SQL Server

2) You write a function that automatically "re-binds" all linked tables to SQL Server each time the app opens, in case any tables have changed. (Again -- not the DATA, but the STRUCTURE -- columns, column names, data types, etc)

3) everything works well, the reports/forms/queries are all perfectly linked to your table structure.

Now, what happens when you change the table names or columns in SQL? Step #2 runs when the user opens their copy of Access, which is wonderful, but now none of the forms,reports, or queries work !!

In other words, by definition, if you change the structure of the tables in SQL, which necessitates a re-linking in Access, then you need to rewrite your Access app as well ! And if you need to re-write your Access app, then there is no point in having each user's copy "dynamically" re-link all of its tables! you will need to roll out a new copy of your app anyway, so just re-link them yourself.

Does this make sense? Not sure what point your book or learning tool is trying to make with their statement about re-linking table dynamically, other than showing off their nifty code.

- Jeff

LaurieW
Starting Member

4 Posts

Posted - 2004-10-25 : 15:35:17

I totally understand what you are saying. I think the book is not talking about relinking because of changes to the table, but just to be sure that all the tables are there. That's the gist I get from it anyway.

I guess I won't worry about it. As you say, maybe they're just trying to show off their code. Thanks.........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources