Linking SQL Server Tables with ODBC (Access 2003)

Topic

New Reply

My database uses a macro called AutoExec which runs the code below. The tblODBCDataSources supplies all the information needed to create the linked tables including the database password.

The problem is that when I launch the database, I am prompted for the SQL Server Password before the AutoExec runs. I do not want users to be prompted for the database password. I thought that AutoExec runs before anything else in an MDB application.

I don’t think there is anything wrong with the function below because if I shift into the database, delete all the linked tables, and run the autoexec macro manually, all the linked tables appear as required.

Any ideas?

Thanks,

Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
Dim strDSN As String
‘ ———————————————
‘ Register ODBC database(s).
‘ ———————————————
Set db = CurrentDb
Set rs = db.OpenRecordset(“Select * From tblODBCDataSources Order By DSN”)

With rs
While Not .EOF
If strDSN rs(“DSN”) Then
DBEngine.RegisterDatabase rs(“DSN”), _
“SQL Server”, _
True, _
“Description=VSS – ” & rs(“DataBase”) & _
Chr(13) & “Server=” & rs(“Server”) & _
Chr(13) & “Database=” & rs(“DataBase”)
End If
strDSN = rs(“DSN”)
‘ ———————————————
‘ Link table.
‘ ———————————————
strTblName = rs(“LocalTableName”)
strConn = “ODBC;”
strConn = strConn & “DSN=” & rs(“DSN”) & “;”
strConn = strConn & “APP=Microsoft Access;”
strConn = strConn & “DATABASE=” & rs(“DataBase”) & “;”
strConn = strConn & “UID=” & rs(“UID”) & “;”
strConn = strConn & “PWD=” & rs(“PWD”) & “;”
strConn = strConn & “TABLE=” & rs(“ODBCTableName”)
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs(“ODBCTableName”), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
‘MsgBox “Refreshed ODBC Data Sources”, vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, “MyApp”
‘Resume Next
Resume CreateODBCLinkedTables_End
End Function

Reply | Quote

Replies

Do you have a startup form? This is opened before AutoExec is run, so you could call the function in the On Open event of the startup form. (I wouldn’t use theOn Load event of the form for this, because this occurs when the first record is loaded, which might be too late)

Reply | Quote

Yes. I do have a startup form. I’ll move the call statement to the OnOpen event and see what happens.

By the way, I will hide the table and hide the database window and disable special keys to prevent SQL credentials from being exposed. If anyone is aware of a more secure way to do this, I would l like to find out. But that is for another post.

Thanks.

Reply | Quote

You can set a password on theVBA project (in the Visual Basic Editor: select Tools | Properties and activate the Security tab).

Reply | Quote

My database uses a macro called AutoExec which runs the code below. The tblODBCDataSources supplies all the information needed to create the linked tables including the database password.

The problem is that when I launch the database, I am prompted for the SQL Server Password before the AutoExec runs. I do not want users to be prompted for the database password. I thought that AutoExec runs before anything else in an MDB application.

I don’t think there is anything wrong with the function below because if I shift into the database, delete all the linked tables, and run the autoexec macro manually, all the linked tables appear as required.

Any ideas?

Thanks,

Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
Dim strDSN As String
‘ ———————————————
‘ Register ODBC database(s).
‘ ———————————————
Set db = CurrentDb
Set rs = db.OpenRecordset(“Select * From tblODBCDataSources Order By DSN”)

With rs
While Not .EOF
If strDSN rs(“DSN”) Then
DBEngine.RegisterDatabase rs(“DSN”), _
“SQL Server”, _
True, _
“Description=VSS – ” & rs(“DataBase”) & _
Chr(13) & “Server=” & rs(“Server”) & _
Chr(13) & “Database=” & rs(“DataBase”)
End If
strDSN = rs(“DSN”)
‘ ———————————————
‘ Link table.
‘ ———————————————
strTblName = rs(“LocalTableName”)
strConn = “ODBC;”
strConn = strConn & “DSN=” & rs(“DSN”) & “;”
strConn = strConn & “APP=Microsoft Access;”
strConn = strConn & “DATABASE=” & rs(“DataBase”) & “;”
strConn = strConn & “UID=” & rs(“UID”) & “;”
strConn = strConn & “PWD=” & rs(“PWD”) & “;”
strConn = strConn & “TABLE=” & rs(“ODBCTableName”)
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs(“ODBCTableName”), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
‘MsgBox “Refreshed ODBC Data Sources”, vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, “MyApp”
‘Resume Next
Resume CreateODBCLinkedTables_End
End Function

What is the code behind the function DoesTblExist(strTblName)?

Reply | Quote

What is the code behind the function DoesTblExist(strTblName)?

Don’t worry i have worked it out, here is my solution:

Code:

Private Function DoesTblExist(strTblName) As Boolean
	Dim tdf As DAO.TableDefs, rs As DAO.Recordset
	On Error Resume Next
	Set rs = CurrentDb.OpenRecordset(strTblName)
'	Set tdf = CurrentDb.TableDefs(strTblName)
	DoesTblExist = (Err = 0)
	Err.Clear
	Set tdf = Nothing
End Function

Reply | Quote