ADO: Unrecognised Database Format error

[Jon von der Heyden]

I've done this a thousand times, yet now I get error 'Unrecognised Database Format' followed by the full name of the workbook. Uploading from Excel to Access:

The upload method:

Public Sub BatchUpload(ByVal command As ADODB.command, ByVal strFile As String)
    'Note to self: Use doc properties to validate file selection
    
    Dim strCommandText As String
    Dim wkbUpload As Workbook
    
    Const strDescription As String = "Error executing batch upload statement!"
    
    Set wkbUpload = ReferenceWorkbook(strFile)
    
    With wkbUpload
        .Sheets(1).Rows("1:6").Delete Shift:=xlUp
        .Close SaveChanges:=True
    End With
    
    strCommandText = "INSERT INTO KPI_TBL SELECT * FROM [Upload$] IN '" & _
                     strFile & "' 'Excel 8.0'"
    
    Call ExecuteCommand(command, strCommandText, strDescription)
Finish:
End Sub

The ExecuteCommand

Private Sub ExecuteCommand(ByVal command As ADODB.command, _
                           ByVal strCommandText As String, _
                           ByVal strDescription As String)
    Dim lngRecordsAffected As Long
    
    command.CommandText = strCommandText
    Call command.Execute(RecordsAffected:=lngRecordsAffected, _
                         Options:=CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
                         
    Call CheckError(lngRecordsAffected, 1, strDescription)
End Sub

And how I'm pulling it together:

Public Sub TestBatchUpload()
    Dim strFile As String
    Dim clsDB As clsManageDB: Set clsDB = New clsManageDB
    Dim objCommand As ADODB.command: Set objCommand = New ADODB.command
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Select"
        If .Show = -1 Then
            strFile = .SelectedItems(1)
        End If
    End With
    
    With clsDB
        .DBpath = Evaluate(ThisWorkbook.Names("str_const_DBPATH").RefersTo)
        objCommand.ActiveConnection = .DBconnection
        Call .BatchUpload(objCommand, strFile)
    End With
    
End Sub

I'm sure I'll get it after an hour or so of further frustration, but can anyone spot anything obviously wrong with the above? The format of the workbook that the upload data is contained in is .xlsx.

 

[RoryA]

You specify Excel 8.0 in the connection, not Excel 12.0 which is what the file actually is.

 

[Jon von der Heyden]

Thanks Rory. I copied a bit of code from the old days. So I made the change to 12 and I get the same error. What gives?

INSERT INTO KPI_TBL SELECT * FROM [Upload$] IN 'C:\Users\Jon von der Heyden\Documents\Excel Design Solutions\002 Projects\001 Siemens\CSCM Common Reporting\Uploads\Test1.xlsx' 'Excel 12.0'

 

[RoryA]

What provider are you using? Jet? (won't work)

 

[Jon von der Heyden]

Yes. I'm sure it used to work. What provider should I be using?

 

[RoryA]

Microsoft.ACE.OLEDB.12.0

Jet will work if the workbook is open (though you get memory leaks then) but not if it's closed since the structure is all wrong for it.

 

[Jon von der Heyden]

I got it just as you replied: it's shown in my PED book Thanks!

So client is on 03, I am developing in 10 and has to be compatible with all new versions in case of upgrade. #*&% sakes! More work! I should have outsourced this stoopid project!

Another quickie. Something I've not done before: I would like to run an update query on the excel file before inserting into the DB. The update would involve join to tables in the DB. Possible? I've never tried it that way.

I'm just being lazy asking; so really a simple yes or maybe a referral to a thread doing that would be a great help.

Kinda like:

UPDATE KPI_TBL INNER JOIN MKPI_TBL ON KPI_TBL.KPI_NAME = MKPI_TBL.KPI_NAME SET KPI_TBL.KPI_DESCRIPTION = [mkpi_tbl]![KPI_DESCRIPTION], KPI_TBL.UNIT_OF_MEASURE = [MKPI_TBL]![KPI_UOM], KPI_TBL.FUNCTION = [MKPI_TBL]![KPI_FUNCTION], KPI_TBL.COMMODITY_CATEGORY = [MKPI_TBL]![KPI_CATEGORY], KPI_TBL.MAJOR_COMMODITY = [MKPI_TBL]![KPI_MAJ_COMMODITY], KPI_TBL.SUB_COMMODITY = [MKPI_TBL]![KPI_MIN_COMMODITY], KPI_TBL.DATE_CREATED = Now(), KPI_TBL.LAST_UPDATED = Now(), KPI_TBL.LOCKED = "FALSE", KPI_TBL.CONCAT = [KPI_TBL]![COUNTRY] & ";" & Format([KPI_TBL]![PERIOD],"mmm-yyyy") & ";" & [KPI_TBL]![SECTOR] & ";" & [KPI_TBL]![DIVISION] & ";" & [KPI_TBL]![BUSINESS_UNIT];

Except where KPI is actually the excel file Upload$ table.

 

[RoryA]

Yes.

 

[RoryA]

For example (this is for two workbooks, but the principle is the same):

Sub GetDataFromTwoWorkbooks()
   ' Sample demonstrating how to return a recordset from two workbooks
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim varData As Variant
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      ' connect to book 1
      .ConnectionString = "Data Source=C:\Test\ADO Test.xls;" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   ' union query with Sheet1 in both books
   strQuery = "SELECT * FROM [Sheet2$] tblA INNER JOIN (SELECT Header2 FROM `C:\Test\ADO Test2.xls`.[Sheet2$]) As tblB ON tblA.[Header2] = tblB.[Header2]"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   
   ' do whatever you need with recordset here
   ActiveSheet.Range("A2").CopyFromRecordset rst
   rst.Close
   Set rst = Nothing
   cn.Close
   Set cn = Nothing
End Sub

 

[Jon von der Heyden]

Sweet! I owe you 5 guinesses