Run Access VBA from Excel

[MarkCBB]

Hi there,

I would like the below code (The code is in excel). to create a Access database, and then import excel data from other file files. the code is not completed yet as I still need to loop a few things. the end result is to import excel data into access database, but because there is a size limitation in access (2GIG), I need the code to crete new access databases and then it will continue with the loop etcc... I know how to do most of that. but I am struggling to get an Access function to run from inside excel.

I am commented where I am getting the error.

Function DoImport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, str_AccessName As String
Dim blnHasFieldNames As Boolean
Dim xlApp As Object, wb_Book As Object, objAccess As Object
Dim DoCmd As Object


Set objAccess = CreateObject("Access.Application")
Set xlApp = CreateObject("Excel.Application")
Set DoCmd = objAccess.Application


blnHasFieldNames = True


strPath = "D:\Dropbox\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Live files\iRAM_ADDIN_APP_01\SSF\SF\SFS\2PBI_DB\"
str_AccessName = "D:\Dropbox\eXceler8\New"
Call objAccess.NewCurrentDatabase(str_AccessName)
'Call objAccess.OpenCurrentDatabase("D:\Dropbox\eXceler8\New.accdb")
objAccess.Visible = True




 strTable = "CLIPPA SALES"
 strFile = Dir(strPath & "*.xlsx")
 
 Do While Len(strFile) > 0
    If InStr(strFile, " MAIN") > 0 Then
        Set wb_Book = xlApp.Workbooks.Open(strPath & strFile)
        wb_Book.Sheets("MAIN").Range("D:M,O:O,P:P,T:T,U:U,V:V,AC:AC,AD:AD,AG:AG,AJ:AJ,AK:AK,AB:AB,AA:AA,X:X").Delete
        strPathFile = strPath & strFile
      
        With objAccess
            .DoCmd.SetWarnings False
            .UserControl = True
            .DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, "MAIN!" ' Gettng the error here <<<<<<<<<<<<<<<<<<<<
            .DoCmd.SetWarnings True
        End With
        
        Debug.Print strFile
        wb_Book.Close False
        Set wb_Book = Nothing
    End If


        Call DeleteImportErrorTables
       strFile = Dir()
       
 Loop
 Set xlApp = Nothing


End Function
Function DeleteImportErrorTables()


 Dim oTable As DAO.TableDef
 
For Each oTable In CurrentDb.TableDefs
If oTable.Name Like "*ImportErrors*" Then
CurrentDb.TableDefs.Delete oTable.Name
End If
Next oTable
 
End Function