CODEWITHSUNDEEP
excelvbams-accessexport
Bryan Sonnier
Bryan Sonnier

Reputation: 29

Export Named Table from Excel to Access

OK. I'm sorry for wasting everyone's time. Like a DUMMY i didn't think simple solution first. The amount of data i am dealing with isn't too large and will actually work better just exporting to an excel file (i'm pretty sure). I would like to thank all that helped (June7, Parfait, and HansUp). The support you guys (everyone on this forum) give has made my job easier by far.

I'm trying to export an Excel Table from my active excel file to an Access database file. I was getting an error at

"con.excecute sql"

"Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Microsoft Access Driver] Query input must contain at least one table or query."

Sub updateAccess()

Dim con As New ADODB.Connection
Dim connectionString As String

Dim sql, newTable As String
Filename = "C:\Desktop\Quote-Size_Contacts.accdb"
connectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & Filename

con.Open connectionString

' Save current table ("ContactsTbl_Data") to another table ("ContactsTbl_Data_yyyymmdd_hh_mmss")
newTable = "Quote-Size_Contacts_" & Format(Date, "yyyymmdd") & "_" & Format(Now, "hhmmss")
sql = "SELECT CODE, STORE INTO " & newTable & "FROM ContactsTbl_Data"
con.Execute sql

' Delete rows of current table ("ContactsTbl_Data")
sql = "DELETE FROM ContactsTbl_Data"
con.Execute sql

' Insert new rows into current table ("ContactsTbl_Data") from my Excel Sheet
sql = "INSERT INTO ContactsTbl_Data ([CODE], [STORE]) " & _
      "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[" & ThisWorkbook.Sheets("Sheet2").Name & "$]"
con.Execute sql

con.Close
Set con = Nothing

End Sub

EDIT:: I'm not sure standard protocol for these forums on cleaning up the code and asking more questions so i'll just put an "Edit" here. I applied the suggestions and matched the fields it was trying to save to my access file. I now get the error: "Method 'Execute' of object '_Connection' failed"

Public Sub updateAccess()

Dim con As New ADODB.Connection
Dim connectionString As String

Dim sql, newTable As String
Filename = "C:\Desktop\Quote-Size_Contacts.accdb"
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & Filename & "'"

con.Open connectionString

' Save current table ("ContactsTbl_Data") to another table ("ContactsTbl_Data_yyyymmdd_hh_mmss")
newTable = "Quote-Size_Contacts_" & Format(Date, "yyyymmdd") & "_" & Format(Now, "hhmmss")
sql = "SELECT Company, Contact, Initials, Position, Address, AddressContd, CityStatePost, MainNo, CellNo, FaxNo, Email INTO [" & newTable & "] FROM ContactsTbl_Data"
con.Execute sql

' Delete rows of current table ("ContactsTbl_Data")
sql = "DELETE FROM ContactsTbl_Data"
con.Execute sql

' Insert new rows into current table ("ContactsTbl_Data") from my Excel Sheet
sql = "INSERT INTO ContactsTbl_Data ([Company], [Contact], [Initials], [Position], [Address], [AddressContd], [CityStatePost], [MainNo], [CellNo], [FaxNo], [Email]) " & _
      "SELECT * FROM [Excel 12.0 Xml;HDR=Yes;Database=" & ThisWorkbook.FullName & "].[" & ThisWorkbook.Sheets("Sheet2").Name & "$]"
con.Execute sql

con.Close
Set con = Nothing

End Sub

Upvotes: 2

Views: 121

Answers (1)

June7
June7

Reputation: 21370

See if this helps.

Table name has hyphen (-) character so use [ ] characters to delimit. Add a space in front of FROM so text doesn't run together in compiled SQL string.

sql = "SELECT CODE, STORE INTO [" & newTable & "] FROM ContactsTbl_Data"

As for connection to Access database, don't think I've ever used or seen Driver, I use Provider:
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & Filename & "'"

Upvotes: 1

Related Questions