lundi 20 avril 2015

Excel VBA to Export Data to MS Access Table - Extended

I'm trying to use the method that was described in one of the other threads that I saw posted on stackoverflow here.

When using the method that was described in that thread (that got the green check) I'm getting an error when running the code. The error pop-ups up a blank message box with no contents.

A couple of things to mention:

(1) I've made sure to select and activate the Microsoft Access 14.0 Object Library in Excel.

(2) I am running the sub procedure from my database worksheet in Excel.

(3) I am then running the AccImport procedure within my code procedure from my wizard worksheet in Excel (separate worksheet).


EXCEL SPREADSHEET SETUP

I can't use screenshots as of yet as I am new to the community but the database worksheet field range is setup as follows.

B1 (Occurrence Date), C1 (Machine), D2 (Cell), E2 (Status), F2 (Issue), G2(Preventative/Corrective), H2 (Assigned To)

B2 (15-APR-2015), C2(machine1), D2(cell1), E2 (0), F2(Test), G2 (Corrective), H2 (nameexample1)


ACCESS DATABASE TABLE IS SETUP AS FOLLOWS:

Table Name: MaintenanceDatabase

ID, Occurrence Date, Machine, Cell, Status, Issue, Preventative/Corrective Assigned To

Here is the code that I am running from the Database worksheet in Excel:

Sub AccImport()

    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "C:\Users\brad.edgar\Desktop\DASHBOARDS\MAINTENANCE\MaintenanceDatbase.accdb"
    acc.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="MaintenanceDatabase", _
        Filename:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, _
        Range:="Database$B1:H2"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing

End Sub

Code Snippet from other Worksheet Object that Runs AccImport:

Public Sub DeleteSelectedRecord()
    Dim CurrentSelectedIndex    As Integer

    ' Assign the currently selected index to CurrentSelectedIndex
    CurrentSelectedIndex = [Database.CurrentIndex]

    ' Move the ListBox Selector
    If [Database.CurrentIndex].Value = [Database.RecordCount] Then    
'Last item on the list
        [Database.CurrentIndex].Value = [Database.CurrentIndex].Value - 1
    End If

    'Copy to Access Database

    Database.AccImport

    ' Delete the entry
    Database.ListObjects("Database").ListRows(CurrentSelectedIndex).Delete

End Sub

Hopefully someone could shed some light into why I'm getting an error.

Thanks in advance for any help.

Cheers,

Brad

Aucun commentaire:

Enregistrer un commentaire