Setting ODBC User ID & Pass in Connection String

Chris_SA

New Member
Joined
Nov 17, 2004
Messages
28
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:

Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

bruco2

New Member
Joined
Jan 25, 2005
Messages
18
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.
 

Forum statistics

Threads
1,148,273
Messages
5,745,794
Members
423,973
Latest member
man_this_is_hard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top