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:
Any help would be awesome - I'm pretty stuck!
Thanks
Chris
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