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

 

[xenou]

I'm not so sure about the references ... you have a lot of references to ado libraries.

You only need these references:
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office
Microsoft Access 12.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

Actually, amend that to:
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office
Microsoft ActiveX Data Objects 2.8 Library
I don't think you are actually automating Access here, either - just pulling data from it with ADO.


Sorry but gotta ask here:

Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

You are using Access 2007, right?

 

[mrllewellyn]

Ok. Changed the name of the Connection. Same error. And the code is highlighted below. I'm looking to see what a "Catastrophic Failure" is now.

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

 

[xenou]

Use the "trick" In post # 7 here to check your connection string:
http://www.mrexcel.com/forum/showthread.php?t=498330

Also get rid of the unneeded (and potentially problematic) references (as noted in my last post).

 

[mrllewellyn]

Wow! That .udl solution is very cool. We'll have to see how this goes after lunch. I am fried. Thanks for all the help!

 

[xenou]

Okay.
I tested your code too and it works fine for me
The use of Connection as a variable doesn't actually stop the code from working although I also don't use object name as variable names in VB. Sometimes VB will "helpfully" auto-instantiate objects when you do this. It's easiest to just avoid the problem.

Another question might be whether there is security on the database - a password or workgroup file, anything like that.

Here, for what it's worth, is my test, but it won't work as is for you since I've only get Jet on this machine, not ACE (and the DB is not the same of course). I noticed that your code in post 6 has two New Connection statements - which seemed to cause no problems but can be cleaned up too.

Again - strongly advise removing the references that aren't actually needed (see my post #11)

Sub ADO_Demo1()
Dim DBFullName As String
Dim Connection_String As String, SQL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Long

    DBFullName = "C:\myTemp\TestDB.mdb"
    Connection_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myTemp\TestDB.mdb;Persist Security Info=False"
    SQL = "SELECT * FROM Employees;"
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open Connection_String
    rs.Open SQL, cn
    
    For col = 0 To rs.Fields.Count - 1
    Worksheets("Sheet1").Range("A1").Offset(0, col).Value = rs.Fields(col).Name
    Next
    
    Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

 

[mrllewellyn]

SUCCESS! Thanks all for the help and sharing! I started from scratch and it works for some reason! Go figure.

I'll have to keep that textfile.udl trick.

 

[pedie]

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

Can someone please tell me how does this work with the password protected database?
The password is "mypass2.32"


Thanks you very much!

 

[Norie]

Pedie

Have you tried searching for ADO connection strings?

There are a lot of sites out there that have pretty comprehensive lists of examples for most types of databases/drivers etc.

 

[Rasm]

Did you test the connectionstring in the graphical part of the ADO - that way you can see if you connect correctly - all using the graphics of the GUI - there is 'test connection' button. - after you get that working - then use code to read the properties. However you will have trouble with access 2007 unless you use MDB files.

 

[Rasm]

Peddie - example of password - where ADO = ADODC control - if you look at the properties in graphical section - you will see authendication on a tab.

AdoTemp.Password = ServerPassword