CreateTableDef 'attributes' option
I am trying to link an ODBC table to a database using CreateTableDef.
When I use the optional attribute of dbAttachSavePWD it works fine, but
there are times when I don't want to save the password.
The help subject on CreateTableDef lists valid attributes such as
dbAttachedODBC, but anything other than dbAttachSavePWD generates an error
3001 - Invalid argument.
Am I doing something wrong here?
Thanks,
Glenn.
Here is my code...
Public Function ConnectODBCtable(UseDatabase As String, _
KeepOld As Boolean, DSN As String, _
TableName As String, SourceTable As String, _
Optional UID As String, Optional PWD As String) As String
Dim dbs As Database
Dim tdf As TableDef
Dim OldLink As String
Dim ConnectString As String
On Error GoTo ConnectODBCtableError
Set dbs = OpenDatabase(UseDatabase)
' Rename old link
OldLink = TableName & "_" & Format(Now(), "YYYYMMDDHHNNSS")
'DoCmd.Rename OldLink, acTable, TableName
dbs.TableDefs(TableName).Name = OldLink
' Link new table
ConnectString = "ODBC;" & "DSN=" & DSN & ";"
If IsMissing(UID) = False Then
ConnectString = ConnectString & "UID=" & UID & ";"
End If
If IsMissing(PWD) = False Then
ConnectString = ConnectString & "PWD=" & PWD & ";"
End If
Set tdf = dbs.CreateTableDef(TableName, dbAttachSavePWD, SourceTable,
ConnectString)
dbs.TableDefs.Append tdf
' Delete old link if required
If KeepOld = False Then DoCmd.DeleteObject acTable, OldLink
ConnectODBCtable = "OK"
ConnectODBCtableExit:
dbs.Close
Set dbs = Nothing
Exit Function
ConnectODBCtableError:
ConnectODBCtable = "Error - " & Err.Number & " - " & Err.Description
Resume ConnectODBCtableExit
End Function
