ExcelBanter - ADODB Connection Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - ADODB Connection Problem (https://www.excelbanter.com/excel-programming/347216-adodb-connection-problem.html)

ADODB Connection Problem

Hi Folks,

I am another newbie to Excel VBA programming. Please bear with me!!

I am trying to bring into Excel some Microsoft Access data via an ADO
connection.

The code in the VBA module looks like this:

====================================

Sub GetRecordset()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String

'ODBC;DSN=MS Access Database;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;DefaultDir=C :\Program
Files\Microsoft Office\OFFICE11\SAMPLES;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

sConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT CustomerID, CompanyName, Address, City, Region,
PostalCode" & _
" FROM Customers" & _
" WHERE (City='London')"

Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

Do While Not adoRs.EOF
sOutput = sOutput & adoRs.Fields(0).Value & "

adoRs.MoveNext
Loop

adoRs.Close
adoConn.Close

sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If

Debug.Print sOutput

Set adoRs = Nothing
Set adoConn = Nothing

End Sub

==============================

However, the following error message keeps popping up every time I run
the above code:

"Run-time error 429
ActiveX component can't create object"

This error message results in the following line of code being
high-lighted:

"Set adoConn = New ADODB.Connection"

What am I doing to stop the Access data not being pulled through to the
Excel spreadsheet?

Any assistance given would be much appreciated.

Spardey
Brisbane, Australia

ADODB Connection Problem

Have you referenced your project to the Microsoft ActiveX Data Objects 2.X
Library? Tools - Referneces -...
--
HTH...

Jim Thomlinson

"spardey" wrote:

Hi Folks,

I am another newbie to Excel VBA programming. Please bear with me!!

I am trying to bring into Excel some Microsoft Access data via an ADO
connection.

The code in the VBA module looks like this:

====================================

Sub GetRecordset()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String

'ODBC;DSN=MS Access Database;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;DefaultDir=C :\Program
Files\Microsoft Office\OFFICE11\SAMPLES;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

sConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT CustomerID, CompanyName, Address, City, Region,
PostalCode" & _
" FROM Customers" & _
" WHERE (City='London')"

Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

Do While Not adoRs.EOF
sOutput = sOutput & adoRs.Fields(0).Value & "

adoRs.MoveNext
Loop

adoRs.Close
adoConn.Close

sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If

Debug.Print sOutput

Set adoRs = Nothing
Set adoConn = Nothing

End Sub

==============================

However, the following error message keeps popping up every time I run
the above code:

"Run-time error 429
ActiveX component can't create object"

This error message results in the following line of code being
high-lighted:

"Set adoConn = New ADODB.Connection"

What am I doing to stop the Access data not being pulled through to the
Excel spreadsheet?

Any assistance given would be much appreciated.

Spardey
Brisbane, Australia

ADODB Connection Problem

In the VAB Macro editor, select Tools - References and then click on
Microsoft ADO.

/Fredrik

"spardey" skrev i meddelandet
oups.com...
Hi Folks,

I am another newbie to Excel VBA programming. Please bear with me!!

I am trying to bring into Excel some Microsoft Access data via an ADO
connection.

The code in the VBA module looks like this:

====================================

Sub GetRecordset()

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim sConn As String
Dim sSql As String
Dim sOutput As String

'ODBC;DSN=MS Access Database;DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;DefaultDir=C :\Program
Files\Microsoft Office\OFFICE11\SAMPLES;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

sConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT CustomerID, CompanyName, Address, City, Region,
PostalCode" & _
" FROM Customers" & _
" WHERE (City='London')"

Set adoConn = New ADODB.Connection
adoConn.Open sConn

Set adoRs = New ADODB.Recordset
adoRs.Open Source:=sSql, _
ActiveConnection:=adoConn

If Not (adoRs.BOF Or adoRs.EOF) Then
adoRs.MoveFirst

Do While Not adoRs.EOF
sOutput = sOutput & adoRs.Fields(0).Value & "

adoRs.MoveNext
Loop

adoRs.Close
adoConn.Close

sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If

Debug.Print sOutput

Set adoRs = Nothing
Set adoConn = Nothing

End Sub

==============================

However, the following error message keeps popping up every time I run
the above code:

"Run-time error 429
ActiveX component can't create object"

This error message results in the following line of code being
high-lighted:

"Set adoConn = New ADODB.Connection"

What am I doing to stop the Access data not being pulled through to the
Excel spreadsheet?

Any assistance given would be much appreciated.

Spardey
Brisbane, Australia

ADODB Connection Problem

Try changing your sConn string to:

SQLTableFileName = "C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
SQLTableFileName & ";User Id=admin;Jet OLEDB:Database Password="""""

This connection string is the most basic and easiest to understand. The
connection object only needs four properties to connect to an Access
database.
1) Provider - this is always Microsoft.Jet.OLEDB.4.0
2) Data Source - name of your access filename
3) User Id - Admin unless you've secured your database
4) Jet OLEDB:Database Password = """" (translates to "" when inside of
a string) unless you've secured your database

ADODB Connection Problem

Vincent wrote:
Try changing your sConn string to:

SQLTableFileName = "C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb"

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
SQLTableFileName & ";User Id=admin;Jet OLEDB:Database Password="""""

This connection string is the most basic and easiest to understand. The
connection object only needs four properties to connect to an Access
database.
1) Provider - this is always Microsoft.Jet.OLEDB.4.0
2) Data Source - name of your access filename
3) User Id - Admin unless you've secured your database
4) Jet OLEDB:Database Password = """" (translates to "" when inside of
a string) unless you've secured your database

Hi Folks,

Thank you all for your advice - but unfortunately it still doesn't want
to work for me!

Yes, I had Referenced the VBA Project to "Microsoft ActiveX Data
Objects 2.8 Library" through VBA Tools - References.

Vincent, I have copied and inserted your suggested code.Still
highlights the same piece of code ("Set adoConn = New
ADODB.Connection") when it falls over.

In the " SQLTableFileName" we are only referring to the database of
Northwind.mbd ; do we need to go one step further and refer to the
Customer table as well (eg Northwind.mdb/Customer.tbl)? Or is the
database name sufficient?

When I run it with the Locals Window on, it shows a value of "Nothing"
for both adoConn and adoRs. Of course sOutput shows a value of "". Is
this of assistance?

Again, thanks folks.

Spardey

ADODB Connection Problem

spardey wrote:
sConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb;" & _
"DefaultDir=C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

"Run-time error 429
ActiveX component can't create object"

Try removing 'ODBC;' from sConn and see if that doesn't do it.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com