Trying to write a query

  • Thread starter Thread starter Philosophaie
  • Start date Start date
P

Philosophaie

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
Cells.Clear
' 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
Next
'Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
 
The two periods in Recordset..Open should be only one and the recordset
should be capitalized.
 
Back
Top