Setting ODBC User ID & Pass in Connection String

[Chris_SA]

Hi I'm using Excel to connect (ADO) to an Access Database (with no passwords), which is querying a SQL 7 DB.

I set up a test Access DB (with its own tables & queries) that I can connect to and return data. But, when I try to run an Access query, that uses tables from the SQL DB (which requires a user id and pass) the ODBC connection fails.

I think that my connection string is providing a password to the Access DB and not to the ODBC (which I think it should be)? How can I change my connection string to do this?

This is what my connection string contains

print cn
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=U:\Speed Ferret Testing\AdHocReports.mdb; Password = pass; User ID = name;

This is the code I'm using:

Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & "; Password=pass User ID=name"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        '.Close
        '.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' all records
        .Open "SELECT * FROM " & TableName, cn, , , adCmdText
        ' filter records
        
        
        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
        Next
        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Any help would be awesome - I'm pretty stuck!

Thanks
Chris

 

[bruco2]

Hi, I think the problem may be in missing apostrophs around the pass and name:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & "; Password='pass' User ID='name'"
Try if it solves your problem.