Need To Insert Values From Excel To Access

[ranjith2523]

Hi Friends,

I have an Excel with Sheet named "Report" where I have Data from Columns A to G like below.

ID664	NPI	Management	Lete	Open	NPI	ASIAPACIFIC
ID898	ATPM	Management	NKI	Open	SRPM	EMEA

I wrote the below code to insert the data from Excel To Access but i am not sure how to write the code to insert or Apend it from Excel Sheet To Access

If i hard code like this "Query = "INSERT INTO Report VALUES (1,2,3,4,5,6,7)" it perfectly inserted on my Access but i need to insert the data from Excel sheet (Sheet Name "Report") from Column A to G.

Can someone please help me on this.

StrProvider = "Microsoft.ACE.OLEDB.12.0;"

StrConnection = "Provider=" & StrProvider & "Data Source=" & StrPath


StrDBConn.Open StrConnection


Query = "INSERT INTO Report VALUES (1,2,3,4,5,6,7)"


StrDBConn.Execute Query

 

[alansidman]

Why not just link the two tables? In Access

External Data-->Import and Link-->New Source Data-->From File--Excel

Any changes tot the Excel file will automatically update the Access Table.

 

[ranjith2523]

Why not just link the two tables? In Access

External Data-->Import and Link-->New Source Data-->From File--Excel

Any changes tot the Excel file will automatically update the Access Table.

Thanks for your reply.

The expectation is need to write SQL Query in Excel VBA to insert the data from Excel sheet (Sheet Name "Report") from Column A to G to MS Access.

 

[alansidman]

What difference does it make. Or is it a case of someone requiring Form over Substance? The shortest route is sometimes the most effective, but it is your case to deal with. Good Luck.

 

[ranjith2523]

I understood that the "Shortest route is sometimes most effective" but here the requirement is that I need to incorporate this part of the vba code in my project.

Instead of the value 1 to 7 i need to refer the columns 1 to7 "Query = "INSERT INTO Report VALUES (1,2,3,4,5,6,7)"

If I execute the above code the query will insert the number 1 to 7 in columns 1 to 7 in my access file.

The VBA code should insert the data from columns 1 to 7 (I mean COL A to G) to my access file. Please tell me how to refer that ?

What difference does it make. Or is it a case of someone requiring Form over Substance? The shortest route is sometimes the most effective, but it is your case to deal with. Good Luc