VBA w/ADO

Neo-fite

Registered User.
Local time
Today, 17:00
Joined
May 17, 2011
Messages
60
Hello,

I have Access/Excel 2007 and am using excel macros with ADO to create an .accdb file and loading a table from excel. I am then running my queries via the macro. I'm not real fluent with this process and the problem that I am running into is that when the .accdb file is created the associated .laccdb file is create...but when I close out of everything, the .laccdb file stays and I can't remove it without actually re-opening the .accdb file and closing.

Is there a line of code that I am missing that is not closing the file/removing the .laccdb?:confused:
 
Post the code you are using to do this so we can see what might need to be modified.
 
Post the code you are using to do this so we can see what might need to be modified.

Thanks, but I don't have the code available, it's a work project. However, I have been looking at a printout of the code and believe my issue is that I need to set my ADOX.table = Nothing.

I'll find out tomorrow, I guess. :D
 
Post the code you are using to do this so we can see what might need to be modified.

Here it is...

Code:
Sub CreateDB_and_Table()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sDB_Path As String

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    'Delete DB is already exists
    On Error Resume Next
        Kill sDB_Path
    On Error GoTo 0

    'Create new DB
    Set cat = New ADOX.Catalog
    cat.Create "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sDB_Path & ";"
    
    'Create Table
    Set tbl = New ADOX.Table
    tbl.Name = "xxxxData"
    
    'Append the Columns
    With tbl.Columns
        .Append "xxxxNo", adDouble
        .Append "Primary Borrower Name", adVarWChar
        .Append "Customer Name", adVarWChar
        .Append "Status", adVarWChar
        .Append "Date1", adDate
                With ![Date1]
                    Set .ParentCatalog = cat
                    .Properties("Nullable") = True
                End With
        .Append "Date2", adDate
                With ![Date2]
                    Set .ParentCatalog = cat
                    .Properties("Nullable") = True
                End With
            .Append "Amount", adCurrency
                With ![Amount]
                    Set .ParentCatalog = cat
                    .Properties("Nullable") = True
                End With
    End With
    cat.Tables.Append tbl
    
    'Close the Connection
    Set rst = Nothing
    Set tbl = Nothing
    Set cat = Nothing

End Sub
 
I believe the problem lies here...

Code:
        .Append "Date1", adDate
                With ![Date1]
                    Set .ParentCatalog = cat
                    .Properties("Nullable") = True
                End With
In the example I had used as my example, it included the following after "True"

Code:
.Properties("Jet OLEDB:Allow Zero Length") = False
However, being in Access 2007, Jet OLEDB isn't used. Is there a comparable line I should use with ACE.OLEDB.12.0?
 

Users who are viewing this thread

Back
Top Bottom