ADO - Catastrophic error

[mrllewellyn]

This is the first time I've tried to set up ADO in an Excel file to get from an Access file. I copied this code from here with minor changes for the difference in the file name and worksheets and I get a catastrophic error. I'm using Excel and Access 2007. Any suggestions?

'http://p2p.wrox.com/excel-vba/76031-how-specify-relative-path-access-2007-ado-connection-string-excel-2007-a.html
'Recordsets http://allenbrowne.com/ser-29.html
'http://www.codeproject.com/KB/database/connectionstrings.asp

Sub ADO_Demo()
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset

'''''''''''''''''''''''''''''''''''''''''''''''''''Database information
DBFullName = ThisWorkbook.Path & "\Database1.accdb"

'''''''''''''''''''''''''''''''''''''''''''''''''''Open the connection
Set Connection = New ADODB.Connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct






'''''''''''''''''''''''''''''''''''''''''''''''''''Create the recordset
 Set Recordset = New ADODB.Recordset
    With Recordset
        Src = "SELECT * FROM Test"
        .Open Source:=Src, ActiveConnection:=Connection
    
        'Write the field names
        For Col = 0 To Recordset.Fields.Count - 1
            Worksheets("Sheet1").Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
        Next
    
        'Write the recordset
        Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset Recordset
    End With
    
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing


End Sub

 

[Trevor G]

First thing to think of have you set the references to ADODB in the VBA Screen?

Next have you adjusted the Database name and location to your own. Have you renamed the code to look for your table in the database?

Finally where does it stop working and what does the debug state?

 

[mrllewellyn]

These are the references I'm using.

 

[mrllewellyn]

The code has changed a little. I am using an example of a coworker now and I created a different test file. He uses .mdb so I did too. I don't know of a reference to check what is the correct .Provider or reference. My error says:"Run-time error '-2147418113"

Sub ADO_Demo()
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset

'''''''''''''''''''''''''''''''''''''''''''''''''''Database information
DBFullName = "P:\NC Finance\Katie\Katie\test.mdb"

'''''''''''''''''''''''''''''''''''''''''''''''''''Open the connection
Set Connection = New ADODB.Connection
With Connection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open DBFullName  ' ****this is where it goes to in debug mode
End With

 

[Trevor G]

I have just copied the code you have used, set the reference you have and all works fine. I have adjusted the path to a database and database name and also the table name, it returns the data, so where does it stop working and what is the message?

Sub ADO_Demo()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
'''''''''''''''''''''''''''''''''''''''''''''''''''Database information
DBFullName = "M:\Access Files\Acc 2000 Sample Queries" & "\QrySmp00.mdb"
'''''''''''''''''''''''''''''''''''''''''''''''''''Open the connection
Set Connection = New ADODB.Connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
'''''''''''''''''''''''''''''''''''''''''''''''''''Create the recordset
Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT * FROM Employees"
.Open Source:=Src, ActiveConnection:=Connection

'Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Worksheets("Sheet1").Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

'Write the recordset
Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

 

[mrllewellyn]

Thanks for testing the code. So my references are okay?...

I moved both the Access and Excel files to my Desktop for testing. Do I need to turn something on in Access for this to work?

The error still says "Catastrophic Failure" and goes to the red text below.


Sub ADO_Demo1()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
'''''''''''''''''''''''''''''''''''''''''''''''''''Database information
DBFullName = "C:\Documents and Settings\JLlewelyn\Desktop" & "\test.mdb"
'''''''''''''''''''''''''''''''''''''''''''''''''''Open the connection
Set Connection = New ADODB.Connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct



'''''''''''''''''''''''''''''''''''''''''''''''''''Create the recordset
Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT * FROM Test"
.Open Source:=Src, ActiveConnection:=Connection

'Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Worksheets("Sheet1").Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next

'Write the recordset
Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With

Set Recordset = Nothing
Connection.Close
Set Connection = Nothing

End Sub

 

[Norie]

Is the only message 'Catastrophic error'?

Try this before you open the connection.

Connecton.CursorLocation=adUseClient

 

[mrllewellyn]

When I insert the code:
Connecton.CursorLocation = adUseClient
it says Run-time error 424, Object Required

Before I inserted the code the message was:
Run-time error '-2147418113", Catastrophic Error

 

[Trevor G]

Yes your references are OK and no you don't need to do anything to the database. I can't see anything wrong with the code.

A couple of sites suggest memory, security in the applications and problem with installation.

 

[Norie]

Where did you put the code?

By the way, and I should have spotted this earlier and it may very well be the root of the problem - don't call your Connection object variable Connection.

Sub ADO_Demo1()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim cnn As ADODB.Connection
Dim Recordset As ADODB.Recordset
 
    '''''''''''''''''''''''''''''''''''''''''''''''''''Database information
    DBFullName = "C:\Documents and Settings\JLlewelyn\Desktop" & "\test.mdb"
    '''''''''''''''''''''''''''''''''''''''''''''''''''Open the connection
    Set cnn = New ADODB.Connection
 
    Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
 
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
 
    cnn.CursorLocation = adUseClient
 
    cnn.Open ConnectionString:=Cnct
 
    '''''''''''''''''''''''''''''''''''''''''''''''''''Create the recordset
    Set Recordset = New ADODB.Recordset
    
    With Recordset
        Src = "SELECT * FROM Test"
        .Open Source:=Src, ActiveConnection:=Connection
        'Write the field names
        For Col = 0 To Recordset.Fields.Count - 1
            Worksheets("Sheet1").Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
        Next
        'Write the recordset
        Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset Recordset
    End With
 
    Set Recordset = Nothing
    
    cnn.Close
    
    Set cnn = Nothing
 
End Sub