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
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