Sub to export excel range to new access table (1 Viewer)

schniggeldorf · Jun 23, 2017

Hi:

I am trying to write a Sub in Excel that would copy a range of cells from a Excel worksheet, and export them as a new table in an Access Database. My code, in Excel VBA is as follows:

Code:

Public Sub ExportWsToAccessTable()
Dim FullOutputPath As String
Dim objAccess As Access.Application
Dim Range As String
Dim DestTable As String
Dim HasFieldNames As Boolean

FullOutputPath = "S:\NEURO\PUBLIC\IMATCH\Database\IMATCH outcomes database.accdb"
Set objAccess = New Access.Application
DestTable = "tblKpImportPre"
HasFieldNames = True
Range = "a1:eu9"
Call objAccess.OpenCurrentDatabase(FullOutputPath)

objAccess.Visible = True

objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, DestTable, FullOutputPath, HasFieldNames, Range
    
objAccess.CloseCurrentDatabase
Set objAccess = Nothing

End Sub

The code compiles OK, but when I try to run it I get error 3073 - "Operation must use an updatable query". I've searched the web, but haven't found anything that quite fits my situation.

Can anybody help me figure this out?

Thanks.

isladogs · Jun 23, 2017

You are trying to export from Excel to Access so the Transfer Spreadsheet line should be acExport

Also try type Excel12xml

Alternately try importing the Excel file when in Access

ashleedawg · Jun 28, 2017

Range is a reserved word; you'll need to pick a different variable name.

Beyond that, there could be a number of things making the recordset "not updateable"; it's tough to say what the problem is without seeing the destination table.

Is there a reason you can't import into Access instead of exporting from Excel?

schniggeldorf · Jun 30, 2017

Thanks to all who offered suggestions. Unfortunately, none of them worked. I eventually concluded that the 'not updateable" problem related to records I was pulling from Access into Excel in earlier code. Because of this, Access apparently saw Excel as a user, and locked its tables.

I found another way to get the data imported into excel from access, and this solved the problem.

However, I remain stuck. My current code is as follows:

Code:

Public Sub ExportWsToAccessTable()
Dim AdoCon As ADODB.connection
Dim AccessFilePath As String
Dim AccessFileName As String
Dim strRange As String
Dim Range As Range
Dim DestTable As String
Dim HasFieldNames As Boolean
Dim WorksheetName As String
Dim password As String
Dim Connection1 As String
Dim Connection2 As String
Dim strSQL As String

Set AdoCon = New ADODB.connection

AccessFilePath = "S:\NEURO\PUBLIC\IMATCH\Database\"
AccessFileName = "IMATCH outcomes database.accdb"
password = "xxxxx"
DestTable = "tblKpImportPre"
WorksheetName = "Pre"
HasFieldNames = True
strRange = "a1:eu9"
Set myRange = ActiveWorkbook.Worksheets(WorksheetName).Range(strRange)
myRange.Select
Connection1 = "Data Source = " & AccessFilePath & AccessFileName & ";"
Connection2 = Connection1 & "JET OLEDB:Database Password= " & password & ";"
strSQL = "INSERT INTO " & DestTable & " " _
    & "SELECT * FROM " & ActiveWorkbook.FullName & "].[" & WorksheetName & "$]"

With AdoCon
    .Provider = "Microsoft.ACE.oledb.12.0"
    .Properties("Extended Properties") = "Excel 15.0 Xml;HDR=YES;IMEX=2;ACCDB=YES"
    If IsNull(password) Then
        .Properties("Data Source") = Connection1
    Else
        .Properties("Data Source") = Connection2
    End If
    [COLOR="Red"].Open[/COLOR]
End With

AdoCon.Execute strSQL

When I attempt to run this, I get an "authentication failed" error on the .Open line indicated in red. I've been beating my head against the wall :banghead: for several days, but I can't figure out what's wrong with my syntax. The password I'm feeding to Access works fine if I open the database manually.

Sub to export excel range to new access table (1 Viewer)

schniggeldorf

Registered User.

isladogs

MVP / VIP

ashleedawg

"Here for a good time"

schniggeldorf

Registered User.

Similar threads

Users who are viewing this thread