Building History Table 1

[SkipVought]

Hi,

I am an Excel guy, and know enough Access to be dangerous.

I am building a table of historic data from 100+ Excel workbooks in one folder.

I thought I could open each workbook an execute an append query, but found out that...

Run-time error '3423':

You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database.

So what is my best option? I could use ADO to open the Excel table. Do I ADD values for each row and UPDATE row by row?

Sub GetXl()
    Dim oFSO, oFolder, oFile, sSQL As String, sConn As String, sPath As String, sDB As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("\\bhdfwfp426.bh.textron.com\M_ctr$\Bagley\Industrial Engineering\Build Plans")
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    For Each oFile In oFolder.files
        sPath = ThePath(oFile.Path)
        
        sDB = Split(oFile.Name, ".")(0)
    
        sConn = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
        sConn = sConn & "Dbq=" & sPath & "\" & sDB & ".xls;"
        sConn = sConn & "DefaultDir=" & sPath & ";"
    [b]
'can't do this it appears[/b][gray]
'        sSQL = "INSERT INTO BldPln_ExcelHist ( TRAVELER, PART_ID, NOMEN, OPER, CC, MACH_GRP, TRAVELER_QTY, OPER_RUN_HOURS, OPER_PST, OPER_LPST, DAYS_IN_AREA_SCHEDULED, CRITICAL_RATIO, TAPE_TRY_STATUS, SS_DATE )"
'        sSQL = sSQL & vbLf[/gray]
'========================
        sSQL = sSQL & "SELECT DISTINCT TRAVELER, PART_ID, NOMEN, OPER, CC, MACH_GRP, TRAVELER_QTY, OPER_RUN_HOURS, OPER_PST, OPER_LPST, DAYS_IN_AREA_SCHEDULED, CRITICAL_RATIO, TAPE_TRY_STATUS, " & Split(oFile.Name, ".")(0)
        sSQL = sSQL & vbLf
        sSQL = sSQL & "FROM [" & sDB & "$]"[gray]
'========================
'        sSQL = sSQL & vbLf
'        sSQL = sSQL & "IN """" ""ODBC;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & sPath & "\" & sDB & ".xls;DefaultDir=" & sPath & """;"
'        DoCmd.RunSQL sSQL
'========================[/gray]
        Debug.Print sSQL
        rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
[b]
'do my update here instead
        [/b]
        rst.Close
        cnn.Close
    Next
End Sub
Function ThePath(FullName As String) As String
    Dim a, i As Integer
    a = Split(FullName, "\")
    For i = 0 To UBound(a) - 1
        ThePath = ThePath & a(i) & "\"
    Next
    ThePath = Left(ThePath, Len(ThePath) - 1)
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

Looks like that code should work except that you haven't opened your ADO connection using the connection string that you created.

You may want to look at DoCmd.TransferSpreadsheet using the acLink option thus bypassing ODBC. With the Excel table linked to Access your import should be possible using the append SQL. Just DROP the linked table when you are done.

In outline

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
       "DummyTable", sPath & "\" & sDB & ".xls, True

sSQL = "INSERT INTO BldPln_ExcelHist ( [blue]... buncha fields ...[/blue] ) " & _
       "SELECT [blue]... buncha fields ...[/blue] " & _
       "FROM DummyTable "

DROP TABLE DummyTable

 

[SkipVought]

Golom,

Thanks for the speedy and sage reply.

I will incorporate the TransferSpreadsheet method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Resolved, with thanks to Golom:

Sub GetXl()
    Dim oFSO, oFolder, oFile, sSQL As String, sPath As String, sDB As String
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("...blah_path....")
    
    For Each oFile In oFolder.files
        sPath = ThePath(oFile.Path)
        
        sDB = Split(oFile.Name, ".")(0)
        
    'import xl into [DummyTable]
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "DummyTable", _
            sPath & "\" & sDB & ".xls", _
            True
            
    'append from [DummyTable] to [BldPln_ExcelHist]
        sSQL = "INSERT INTO BldPln_ExcelHist (...blah_fields...)"
        sSQL = sSQL & vbLf
        sSQL = sSQL & "SELECT DISTINCT ...blah_fields..."
        sSQL = sSQL & vbLf
        sSQL = sSQL & "FROM [DummyTable]"
        
        DoCmd.RunSQL sSQL
        
    'DROP [DummyTable]
        DoCmd.DeleteObject acTable, "DummyTable"
    Next
    
    Set oFolder = Nothing
    Set oFSO = Nothing
End Sub
Function ThePath(FullName As String) As String
    Dim a, i As Integer
    a = Split(FullName, "\")
    For i = 0 To UBound(a) - 1
        ThePath = ThePath & a(i) & "\"
    Next
    ThePath = Left(ThePath, Len(ThePath) - 1)
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}