Access 2000 DSN-Less connections

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

I like the idea of using DSN-less connections to SQL Server as described
by Doug Steele in his August, 2004 article in the Smart Access newsletter.

My question relates to preserving the table description in the Tables
tab of Access. This description is lost when the old TableDef object is
deleted below, and the new one is created. Is there a way to preserve
the definition, since this is an important part of the application's
documentation?

Bill

' Delete the existing TableDef object
dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName

' Create a new TableDef object, using the DSN-less connection
Set tdfCurrent=dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = strConnect
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent
 
Jeez. Didn't even think of that!

You could change the TableDetails type declaration to hold a Description as
well. I'd suggest making it a Variant, though, so that you can store Null
for those tables that don't have descriptions.

Add a new declaration for:

Dim prpCurr As DAO.Property

Change the section of code that populates the details of the linked tables
to something like:

On Error Resume Next
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 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
Next
On Error GoTo Err_FixConnections

Then, when you're creating the new tabledef objects, use the following
instead:

Set tdfCurrent =
dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
If IsNull(typNewTables(intLoop).Description) = False Then
Set prpCurr = tdfCurr.CreateProperty("Description", _
dbText, typNewTables(intLoop).Description)
tdfCurr.Properties.Append prpCurr
End If
dbCurrent.TableDefs.Append tdfCurrent


(Warning: the above is untested air-code, but barring any typos on my part,
it should work fine)

I'll try and update http://www.accessmvp.com/djsteele/DSNLessLinks.html with
this refinement soon!
 
Doug,

I'm not sure this posted correctly last night so I'm giving it another try:

I'm getting a run-time error 3219 - Invalid Operation on the following line:

tdfCurrent.Properties.Append prpCurr

prpCurr does contain the description of the table at this point.

Any thoughts on the cause of this?

Bill
 
I started to test the code today, and ran into problems (that was one of
them). I'll try & figure out what's going on, and post back tomorrow (sorry,
but I don't have the ability to test against SQL Server at home)
 
Doug,

Your new code works great! I added one additional refinement which may
only apply to me. In my case I had up sized my Jet back-end database to
SQL Server several months ago. In the meanwhile I had added several new
tables to the Jet back-end. So when I ran the DSNLess code it found
these tables that did not exist in SQL server. See the error handling I
added for the resulting error 3011.

One question - is there a way, before the table is deleted from the
front-end mdb, to see whether the table exists in the SQL Server
database? The user may want these table links to remain in the
front-end so they can be fixed manually. The error code that I added
does inform them of these table names, and the fact that the table links
will be deleted from the front-end.

Here's my revised code.

Bill

' will get an error 3011- table not found if this table does not
' exist in the destination database
' user can continue linking tables or stop linking
' this table will be deleted from the front-end mdb

dbCurrent.TableDefs.Append tdfCurrent

' Where it existed, add the Description property to the new table.

If IsNull(typNewTables(intLoop).Description) = False Then
strDescription = CStr(typNewTables(intLoop).Description)
Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText,
strDescription)
tdfCurrent.Properties.Append prpCurrent
End If

' Where it existed, create the __UniqueIndex index on the new table.

If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If

Destination_Table_Not_Found:

Next

End_FixConnections:
Set tdfCurrent = Nothing
Set dbCurrent = Nothing
Exit Sub

Err_FixConnections:
' Specific error trapping added for Error 3291
' (Syntax error in CREATE INDEX statement.), since that's what many
' people were encountering with the old code.
' Also added error trapping for Error 3270 (Property Not Found.)
' to handle tables which don't have a description.

Select Case Err.Number

Case 3011 ' the destination table was not found in the
destination database

Select Case MsgBox("Table " & tdfCurrent.SourceTableName & " was
not found in the destination database " & vbCrLf & "and will be removed
from the front-end mdb." & vbCrLf & vbCrLf & "Click OK to continue
linking tables or Cancel to stop linking tables." ,vbOKCancel Or
vbExclamation Or vbDefaultButton1, "Destination Table Was Not Found")

Case vbOK

Resume Destination_Table_Not_Found

Case vbCancel

Resume End_FixConnections

End Select

Case 3270
Resume Next
Case 3291
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL, _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
Case Else
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
End Select
 
What you could do is create the new TableDef object with a temporary name.
If that works, delete the Old TableDef object and rename the new one you
just created.
 
Doug,

The error 3011 is occurring when I try to append the new tdfCurrent:

dbCurrent.TableDefs.Append tdfCurrent

I'm not sure how to create a new table in the SQL Server database from
my front-end mdb. It appears the table must already exist in SQL Server.

Bill
 
I wasn't talking about creating new tables in SQL Server.


My suggestion was to create the new linked table before you delete the old
one. If you get the error (3011) when you try to create the new table, then
the table musn't exist in the SQL Server database, so don't delete the old
linked table in your Access front-end. If you don't get the error, delete
the old linked table, and rename the new one.
 
Back
Top