Trying to write a query



Sub Data
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Long
' Database information
DBFullName = "E:\Employees\Data.accdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
'Create RecordSet
Set Recordset = New ADODB.Recordset

'Name of the table is: Hourly:Table

query="SELECT * FROM 'lname' and 'fname' and 'email' "
query=query & "Where lname='Anderson' and fname='John' "
query=query & "and email='(e-mail address removed)'"

Recordset..Open Source:=query, ActiveConnection:=Connection

' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
'Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Set Connection = Nothing
End Sub


The two periods in Recordset..Open should be only one and the recordset
should be capitalized.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question