PDA

View Full Version : Solved: Please help me compile this code



flowergirl
08-15-2011, 09:16 PM
This code is supposed to import from Access to Excel:

Sub AccessToExcel()
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet2")
dbFileName = "C:\Documents and Settings\ADRY_7258\My Documents\Test_Access_db.accdb"
dbConnection.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName _
& ";Persist Security Info=False;"

strSQL = "SELECT Test_Access_db.* from Test_Access_db;"

DestinationSheet.Cells.Clear

With dbConnection
.Open
.Cursorlocation = adUseClinet
End With

With dbRecordset
.Open strSQL, dbConnection
Set ActiveConnection = Nothing
DestinationSheet.Range("A2").CopyFromRecordset dbRecordset
DestinationSheet.Range("A1:C1").Value = Array("Employee ID", "Employee Name", "Employee Salary")

dbRecordset.Close
dbConnection.Close

Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing
End Sub

flowergirl
08-15-2011, 09:56 PM
The matter has been resolved.

Thanks.

Aussiebear
08-15-2011, 11:58 PM
So, was the issue, how to compile or an error resulting from a compile attempt?

Bob Phillips
08-16-2011, 02:54 AM
Maybe something to do with this (http://www.vbaexpress.com/forum/showthread.php?t=38654) Ted

Aussiebear
08-16-2011, 02:46 PM
So it seems. I was misled by the thread title.