ODBC From Access 2003 to Sql Server 2003

[Profector]

Hello all,

I'll trying to query a linked SQL table, from SQL Server 2003. Everything works fine on my computer. I have the data connection on my computer and have using these tables for a couple of years. When I share the front end with other users it fails to connect when they try to run the report/query. I'm assuming the other users need the same ODBC. Is that right? Is there a way for me to setup that connection in a query or somewhere else so it's only available as long as the query is running?

If it matters, everyone is pulling the information for reports and nothing is being written back. The connection requires a password.

Thank you

 

[SOS]

Check this out

http://www.access-programmers.co.uk/forums/showthread.php?t=158159

 

[Profector]

Thanks for the link. The source article is a good reference too.

http://support.microsoft.com/kb/892490

 

[Profector]

I couldn't get it work. I get an error that reads "expected end of statement" on the line that starts with "tdfCurrent.Connect =" and everyline after that till "tdfCurrent.SourceTableName = dbo_vICLocation". What am I doing wrong?

Private Sub Form_Load()

Set tdfCurrent = CurrentDb.CreateTableDef(dbo.vICLocation)
tdfCurrent.Connect = oConn.Open "ODBC;Driver={SQL Native Client};" & _
           "Server=INFINITYlENEXA;" & _
           "Database=INFINITY;" & _
           "Uid=myusrname;" & _
           "Pwd=myPassword"
tdfCurrent.SourceTableName = dbo_vICLocation
CurrentDb.TableDefs.Append tdfCurrent


End Sub

 

[Profector]

I tried something different and got "Object required (Error 424)" on line:
Set tdfCurrent = Currentdb.CreateTableDef(dbo.vICLocation)

Private Sub Form_Load()

Dim Currentdb As DAO.Database
Dim tdfCurrent As DAO.TableDef

Set tdfCurrent = Currentdb.CreateTableDef(dbo.vICLocation)
tdfCurrent.Connect = "ODBC;Driver={SQL Native Client};" & _
           "Server=INFINITYlENEXA;" & _
           "Database=INFINITY;" & _
           "Uid=LARRYF;" & _
           "Pwd=myPassword;"
tdfCurrent.SourceTableName = dbo_vICLocation
Currentdb.TableDefs.Append tdfCurrent

End Sub

 

[Profector]

I dont know how it works, but it works.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In Currentdb.TableDefs
        If td.Name = stLocalTableName Then
            Currentdb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = Currentdb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    Currentdb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function


Private Sub Form_Load()

If AttachDSNLessTable("vICLocation", "vICLocation", "INFINITYLENEXA", "Table1", "something", "something") Then

End If

End Sub