msquared99
Member
I have a database (Access) that contains 64 columns of data. I am only interested in pulling 14 of those columns into Excel.
My code works to pull all the data into. So, how do I choose only the columns of data that I want?
Here is the code:
Sub Import_AccessData()
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long
'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)
'Path to the database.
stDB = "C:My DocumentsTestConversion DB 2012.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"
'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM serviceinfo"
'Clear the worksheet.
wsSheet1.Range("A1").CurrentRegion.Clear
With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With
With rst1
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With
With wsSheet1
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
End With
'Release objects from the memory.
rst1.Close
Set rst1 = Nothing
Set rst2 = Nothing
cnt.Close
Set cnt = Nothing
End Sub
My code works to pull all the data into. So, how do I choose only the columns of data that I want?
Here is the code:
Sub Import_AccessData()
Dim cnt As ADODB.Connection
Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
Dim stDB As String, stSQL1 As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet
Dim lnField As Long, lnCount As Long
'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst1 = New ADODB.Recordset
Set wbBook = ThisWorkbook
Set wsSheet1 = wbBook.Worksheets(1)
'Path to the database.
stDB = "C:My DocumentsTestConversion DB 2012.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"
'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM serviceinfo"
'Clear the worksheet.
wsSheet1.Range("A1").CurrentRegion.Clear
With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With
With rst1
.Open stSQL1, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With
With wsSheet1
.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
End With
'Release objects from the memory.
rst1.Close
Set rst1 = Nothing
Set rst2 = Nothing
cnt.Close
Set cnt = Nothing
End Sub
