Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking SQL Server Tables with ODBC (Access 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking SQL Server Tables with ODBC (Access 2003)

    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)

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking SQL Server Tables with ODBC (Access 2003)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking SQL Server Tables with ODBC (Access 2003)

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

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='aap2' post='759902' date='20-Feb-2009 08:12']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[/quote]

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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='patt' post='780581' date='19-Jun-2009 12:48']What is the code behind the function DoesTblExist(strTblName)?[/quote]
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •