Import MS Access data into MS Excel using VBA...

[Steve Thompson]

Hello all,

I've been left with the dubious task of importing Microsoft Access data into
an Microsoft Excel spreadsheet using ADO. Unfortunately, it's been years
since I had to do this type of project. Would anyone be so kind as to point
me to a website that shows the code to do this type of stuff? I will be
using ADO. It seems to me that I used DAO the last time I had to do this
type of import, but again that was years ago. I'm sure things have changed
a lot in a few years.


Thanks a bunch,

Steve

 

[Ron de Bruin]

I have a page on my site Steve
See also the link to OLE's site on the page

http://www.rondebruin.nl/accessexcel.htm

 

[Steve Thompson]

I have a page on my site Steve
See also the link to OLE's site on the page

http://www.rondebruin.nl/accessexcel.htm

Thank you Ron, I appreciate it.

Steve

 

[Steve Thompson]

Hello all,

I've been left with the dubious task of importing Microsoft Access data
into an Microsoft Excel spreadsheet using ADO. Unfortunately, it's been
years since I had to do this type of project. Would anyone be so kind as
to point me to a website that shows the code to do this type of stuff? I
will be using ADO. It seems to me that I used DAO the last time I had to
do this type of import, but again that was years ago. I'm sure things have
changed a lot in a few years.


Thanks a bunch,

Steve

Ron,

I'm getting an error 3705; See below (watch out for linw wrap). Do you know
how to fix this?


Sub ImportDataTwo()
Dim conConnection As ADODB.Connection
Dim lngColIndex As Long
Dim rstRecordset As ADODB.Recordset
Dim strFullPath As String
Dim strTableName As String
Dim rngTarget As Range
Dim strSQL As String

'Set rngTarget = rngTarget.Cells(1, 1)
strFullPath = "C:\Source\VB6\DMR\Valero.mdb"
strTableName = "MonthlyDataForExcel"


Set conConnection = New ADODB.Connection
conConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDBatabase
Password=Administrator; Data Source=" & strFullPath & ";Persist Security
Info=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Engine Type=5"

Set rstRecordset = New ADODB.Recordset

With rstRecordset
.Open strTableName, conConnection, adOpenStatic, , adCmdTable

'Run-time error '3705':
'
'Operation is not allowed when the object is open.
.Open "SELECT * FROM " & strTableName & ";" & conConnection & " , ,
adCmdText" ' < -- Error Here

rngTarget = "A1"

For lngColIndex = 0 To rstRecordset.Fields.Count - 1
rngTarget.Offset(0, lngColIndex).Value =
rstRecordset.Fields(lngColIndex).Name
Next

rngTarget.Offset(1, 0).CopyFromRecordset rstRecordset
End With

rstRecordset.Close
Set rstRecordset = Nothing

conConnection.Close
Set conConnection = Nothing
End Sub

 

[Guest]

I'm getting an error 3705; See below (watch out for linw wrap). Do you know
how to fix this?

With rstRecordset
.Open strTableName, conConnection, adOpenStatic, , adCmdTable
.Open "SELECT * FROM " & strTableName & ";" & conConnection & " , ,
adCmdText" ' < -- Error Here

I don't think you can open the same recordset twice.
It looks like both .Open... lines try to do the same thing, so you only need
one.

 

[Steve Thompson]

I don't think you can open the same recordset twice.
It looks like both .Open... lines try to do the same thing, so you only
need one.

My mistake. the line ".Open strTableName, conConnection, adOpenStatic, ,
adCmdTable" should have been delete in my post.

But even still, I get the same error. Any siuggestions?

Steve

 

[Guest]

My mistake. the line ".Open strTableName, conConnection, adOpenStatic, ,
adCmdTable" should have been delete in my post.

But even still, I get the same error. Any siuggestions?

Steve

Sorry, I overlooked that.

This is the syntax for ADO recordset.Open:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options


Source is your SQL statement ("SELECT * FROM " & strTableName),
ActiveConnection is conConnection and Options is adCmdText, so your call
could look like this:

..Open Source:="SELECT * FROM " & strTableName, _
ActiveConnection:=conConnection, _
Options:=adCmdText

Or your procedyre like this:


Sub ImportDataTwo()

Dim conConnection As ADODB.Connection
Dim lngColIndex As Long
Dim rstRecordset As ADODB.Recordset
Dim strFullPath As String
Dim strTableName As String
Dim rngTarget As Range
Dim strSQL As String

strFullPath = "C:\Source\VB6\DMR\Valero.mdb"
strTableName = "MonthlyDataForExcel"

Set conConnection = New ADODB.Connection
conConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDBatabase
Password=Administrator; Data Source=" & strFullPath & ";Persist Security
Info=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Engine Type=5"

Set rstRecordset = New ADODB.Recordset

With rstRecordset
..Open Source:="SELECT * FROM " & strTableName, _
ActiveConnection:=conConnection, _
Options:=adCmdText
End With

Set rngTarget = Range("A1")

For lngColIndex = 0 To rstRecordset.Fields.Count - 1
rngTarget.Offset(0, lngColIndex).Value = rstRecordset.Fields(lngColIndex).Name
Next

rngTarget.Offset(1, 0).CopyFromRecordset rstRecordset


rstRecordset.Close
Set rstRecordset = Nothing

conConnection.Close
Set conConnection = Nothing
End Sub


Hope this helps some.