Excel VBA push data to Access

[Doedtman]

This one is not going to be easy! I need some help to know if what I'm doing is even possible. I have a lot of files that create a tab that needs to be imported into a table in access. The code I have so far imports the data into the access table, and works like a charm. The issue is that sometimes there's an error (I haven't been able to narrow this down to the reason why yet). Can we somehow modify this code to tell us if there's an error and if so which line or column or both? Let me know if you need any more specifics. Thanks for any help!

Option Explicit


Sub AccImport()
Dim lrow As Integer
Dim rng As String
Dim acc As New Access.Application
If Sheets("Input").Range("M1").Value = ChrW(&H2713) Then 'testing if it's already been imported into access.
    MsgBox ("The data has already been imported.  Exiting macro...")
 Exit Sub
 Else

'Excel range for data that needs imported to access
    lrow = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row
    rng = "Summary$A1:G" & lrow

Find access database and import
    acc.OpenCurrentDatabase "C:\Users\Test\Finalfile.accdb"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tblSummary", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:=rng
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

'Mark completed on the excel tab
    Sheets("Input").Range("M1").Value = ChrW(&H2713)
    MsgBox ("Summary has been imported.")
End If
End Sub

 

[Joe4]

Most of the time, when there is an error on loading an Excel file into Access, it is usually a Data Type error, such as trying to import a Text entry into a Numeric field.
Unfortunately, Access error messages are often less than helpful in locating/identifying the errors.

When you experience the issues, what is happening?
- Does it import none of the records?
- Does it import some of the records?

If the later, you will usually also see a table that starts with the same name as the table you are trying to import into following by the words "_Import_Errors". If you see that table, then you can view the results to see the records and fields it is taking issue with.

If the former, unfortunately Access usually doesn't tell you exactly what/where the issue is.

One method that should work in theory (but I am not sure how to code in Excel VBA) is to get the record count of the Access table you are trying to import into BEFORE and AFTER your import, and compare that to the record count of the Excel tab you are importing, and checking to see if they are the same (and if not, return some sort of message or log it).

Quite frankly, I find importing Excel data into Access can be flaky and a pain to work with, as you cannot set-up Import Specifications for it, so Access tries to figure out data types based on the first ten records of data. I usually prefer to export my Excel data to a tab-delimited text file, and import that into Access, where you can set up Import Specifications and explicitly declare the data type of each field yourself.

 

[Doedtman]

Thank you for the reply! The issue was that an amount didn't import. All of the lines imported okay, but a few pulled zero dollar amounts for some reason. I agree with your workaround though. That was the route I was planning to take if I didn't get any feedback. I'm not sure how to do it yet, so I'll need to research, but I should be able to write a macro to query the data and sum it up to compare totals then delete the query out of the file. Thank you for the help!

 

[Joe4]

You are welcome.

I would recommend looking at the records in Excel that would not import. Are those dollar amounts entered as Text in Excel (one dead give-away is they are left-justified, as numbers are right-justified in Excel, by default).

 

[Doedtman]

Thanks, but the data is generated through a macro and all formatting is the same, so it doesn't make sense. The other alternative is that someone deleted the data in access after it was imported, so at least if I have a check of some sort I can narrow down the issue. Thanks for your help!