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]

Okay, this didn't work yesterday but it did work today! Dunno. I read on one post to keep special characters out of passwords. Maybe stick to alpha-numeric passwords.

Here was my successful connection (the password was 123):

Sub Foo()
Dim cn As ADODB.Connection
Dim Connection_String As String


Set cn = New Connection


Connection_String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Documents and Settings\USERNAME\Desktop\db1.accdb;" & _
    "Persist Security Info=False;Jet OLEDB:Database Password=123"

cn.Open Connection_String
If cn.State = ObjectStateEnum.adStateOpen Then
    MsgBox "Open!"
    cn.Close
End If



End Sub

 

[pedie]

Thanks all, Thanks Xen! It works perfect now...

Thank you very very much...this helps.


Norie, i did search and also found a lot of examples too but since this is very new i just didnt know where to input what but thanks again for always helping.