"bsn" <bsnsnabelaoncabledotdk> wrote in message
news:43***********************@dread12.news.tele.d k...
"Anthony England" <ae******@oops.co.uk> The syntax is not quite right, you are missing the IN ""
This format is useful if you needed to set other attributes, such as a
password, then you need to end up with something like:
SELECT * FROM MyTable IN "" [MS Access;PWD=secret;DATABASE=C:\Test.mdb]
If I try to run the sql in QBE, then it goes well, but not in my VBA code:
Sql = "SELECT * FROM Firma IN """" [MS Access;DATABASE=" & strPath &
strFileName & ";]"
RS.Open Sql
Debug.Print Rs!FirmaID
Bjarne
Well perhaps the mistake is elsewhere in the code. Try opening a recordset
for a normal local table, does it work? I guess because you call rs.open
you are coding using the ADO object library, but in any case you need to
show a full bit of code. Here are two examples - one using DAO and the
other ADO:
Public Sub DAO_Test()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngCount As Long
Set dbs = CurrentDb
' strSQL = "SELECT * FROM tblContacts IN ""C:\Documents and
Settings\Test.mdb"""
strSQL = "SELECT * FROM tblContacts IN """" [MS Access;" & _
"DATABASE=C:\Documents and Settings\Test.mdb]"
Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF
lngCount = lngCount + 1
rst.MoveNext
Wend
MsgBox "There are " & CStr(lngCount) & " records.", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
Public Sub ADO_Test()
On Error GoTo Err_Handler
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngCount As Long
' strSQL = "SELECT * FROM tblContacts IN ""C:\Documents and
Settings\Test.mdb"""
strSQL = "SELECT * FROM tblContacts IN """" [MS Access;" & _
"DATABASE=C:\Documents and Settings\Test.mdb]"
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection
While Not rst.EOF
lngCount = lngCount + 1
rst.MoveNext
Wend
MsgBox "There are " & CStr(lngCount) & " records.", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
If rst.State > adStateOpen Then
rst.Close
End If
Set rst = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
