Exporting ListObject from Excel to Access

Question

I am writing a macro that will run from an excel workbook and export out one of the ListObjects to my access file.

I have this VBA code at the moment:

Sub AccessImport()

' Create connection
Dim Path As String
Dim conn As Object
Dim connectstr As String
Dim recordset As Object
Dim strSQL As String

Path = "P:\CALIBRE-YSP Implementation\11 General\CDM Database"

Set conn = CreateObject("ADODB.Connection")

connectstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Path & "\CDM_Database_DataOnly.mdb;"

strSQL = "SELECT * INTO DeliverablesLivesheet FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"


conn.Open connectstr
Set recordset = conn.Execute(strSQL)

recordset.Close
Set recordset = Nothing

conn.Close
Set conn = Nothing

End Sub

It is supposed to take a ListObject from Excel then transfer the data to a new access table, overwriting the old one.

It throws an error when it tries to execute the SQL:

strSQL = "SELECT * INTO DeliverablesLivesheet FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"

So I guess something is wrong with the SQL?

I can't seem to work it out though

Thanks

Whoops what I meant was it throws an error on execute cause of the SQL string. I'll amend the Q — KillerSnail
– KillerSnail, Commented Apr 3, 2013 at 5:41

score 1 · Accepted Answer · 2013-04-03 11:22:25Z

1

strSQL="insert into DeliverablesLivesheet Select * FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"

edited Apr 3, 2013 at 11:22

answered Apr 3, 2013 at 5:37

user2230817

Sign up to request clarification or add additional context in comments.

2 Comments

KillerSnail Over a year ago

I want to overwrite the existing table. This string also throws an error on: Select * FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];)

KillerSnail Over a year ago

The DB is not letting me write the data. I wrote a string to clear the existing data. that works. I get runtime error '-2147217911 (80040e09)' i tried to add the code: conn.Mode = adModeReadWrite but that doesn't seem to do anything.

KillerSnail · Accepted Answer · 2013-04-03 06:38:34Z

0

Ok I didn't realise that the connection string works like:

[Excel 8.0;HDR=YES;DATABASE={address of file}].[{sheet name}]

I had the name of the ListObject in there.

Now I get stuck with "Database or object is read only.

answered Apr 3, 2013 at 6:38

KillerSnail

3,64113 gold badges51 silver badges67 bronze badges

Collectives™ on Stack Overflow

Exporting ListObject from Excel to Access

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related