Adodb Insert from excel range

[drag1c]

Hi all,

I've read several topics on forum and couldn't be able to set up script to INSERT data from Excel Table specified range to Microsoft Access database over ADODB.

Currently, I am using ADODB to read data from database (material master, version of file, users), but how to do Insert thing?

I would like to insert Sheet named Material list with range:

Sheet3.Range("A13", Sheet3.Range("H13").End(xlDown))

into database table named: db_workplans

How should my code looks like?

Here below is example of my code to read data:

Private Sub MatMaster() 'GET material master from database

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("matmaster")

sh.Rows("2:" & Rows.Count).ClearContents

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim qry As String, i As Integer
Dim n As Long
 
qry = "SELECT * FROM db_materialmaster"
  
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "URL" & ";" & "Jet OLEDB:Database Password= PASS"

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

sh.Range("A2").CopyFromRecordset rst
  
rst.Close
cnn.Close

Set cnn = Nothing

End Sub

 

[RoryA]

You'll need to figure out the address of the range you want to insert from, then the code would look something like:

cnn.Execute "INSERT INTO Table1 ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6]) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & pathToWorkbook & "].[Material list$A1:H50]"

 

[drag1c]

You'll need to figure out the address of the range you want to insert from, then the code would look something like:

cnn.Execute "INSERT INTO Table1 ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6]) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & pathToWorkbook & "].[Material list$A1:H50]"

Clear.
Is it possible to use something like this, in this syntax:

cnn.Execute "INSERT INTO Table1 ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6]) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & pathToWorkbook & "].[Material list$] WHERE 'Material' IS NOT NULL"

Idea would be to not consider empty rows (if there is nothing in material column/cell, then row is empty).

 

[RoryA]

Field names should be in square brackets, not quotes, but otherwise yes something like that should work.

 

[drag1c]

Here is code with error:

Sub InsertRecord() 'Insert record from Excel to Database

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim DBPath As String
    Dim stSQL As String

    DBPath = ActiveWorkbook.Path & "\main.accdb"
    
    Set cn = CreateObject("ADODB.Connection")

    stSQL = "INSERT INTO db_workplans SELECT * FROM [Excel 12.0;HDR=YES;IMEX=1;Database=" & "***Workbook Path***" & "].[Material list$A12:H1052]"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & ";" & "Jet OLEDB:Database Password= ***PASSWORD***"
    Set rs = cn.Execute(stSQL)

    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Routing Number is header of column where is asd.

by stSQL, I declared HDR=YES and Sheet Material List starts from Header (A12 cell).
If I change it to A13, then error is the same, it's just written instead "Routing Number" -> "asd".
What could cause error? Could it be empty cells in row? If so, how to avoid it?

 

[drag1c]

Hi, please close this topic. I've done it.

There was difference in header names between access table and excel file.

Thank you for all your help !

 

[aribadabar]

Hi, please close this topic. I've done it.

There was difference in header names between access table and excel file.

Thank you for all your help !

Hi, I have almost identical question to yours. Would you please post the final code that worked for you?

Many thanks!

 

[drag1c]

Hi, I have almost identical question to yours. Would you please post the final code that worked for you?

Many thanks!

Uh.. It was a bit long ago.
Check header names in excel and table column names in db.