Writing/Reading from a Table

[gyferlotus]

I have one Excel row populated by:
1 2 3 <-- First Row
I want to take the row and insert it in a Access Database.

The problem is that if I run the code a second time, the table won't
change. The table called MyTable always stays with just 1 row of (1 2 3)
I want the Access Table to keep storing, the first row that im adding

So the output of the Table should be
1 2 3
1 2 3
1 2 3
1 2 3
etc

Dim sConnect As String, sSQL As String
Dim db As ADODB.Connection, Str As String, Source As String, Origin As String
On Error Resume Next
Origin = "C:\Users\Sam\Desktop\book2.xlsx"
Source = "C:\Users\Sam\Desktop\Database4.accdb"

Str = "[Sheet1$A1:C1]"         
 
Set db = New ADODB.Connection
db.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Source & ";Persist Security Info=False;"
  
sConnect = "'" & Origin & "' 'Excel 8.0;HDR=Yes;'"
sSQL = "SELECT * INTO " & "MyTable" & " FROM " & Str & " IN " & sConnect
db.Execute sSQL

Could anyone help me fix this?
Also I would like to know which would be a good SQL command to see
the contents of the table called "MyTable".
Thanks in advance

 

[xenou]

SELECT INTO is a command that creates a new table (in Access, this is called a "Make Table" query).

What you want is to use an INSERT INTO statement. In general terms something like:
INSERT INTO Access_Table SELECT * FROM Excel_Table;

See if that makes sense ... if you run into trouble post back (I'm not up to snuff on Excel worksheet syntax with ADO as I do that only rarely, otherwise I'd post a more detailed response).

ξ

Note,
On this last question:

Also I would like to know which would be a good SQL command to see
the contents of the table called "MyTable".

This is kind of tricky. As far as the raw mechanics are concerned, its just "SELECT * FROM MyTable;". The thing is, how to see it ... . Simplest of course is to open Access and view the table. If you want to see it from Excel, you can drop the results of the query into a recordset, and use the copyfromrecordset command to dump it onto a spreadsheet. Unlike MSQuery, you don't have a graphical interface with ADO. If we wanted to really get fancy, we'd create a windows form, and add a data grid to it ... that wouldn't be too hard, even with free tools like Visual Studio Express. Any takers... ?

 

[gyferlotus]

Thanks a lot xenou that helped me a lot!!

 

[gyferlotus]

Insert into is working but..

If I use this line..

sSQL = "INSERT INTO G (U,CM,M,MI,P,PM,R,RPM) Values(1,2,3,4,5,6,7,8)"   ''Works
sSQL = "INSERT INTO G (U,CM,M,MI,P,PM,R,RPM) Values(WorkS.Cells(2,1),2,3,4,5,6,7,8)" ''Doesnt works

Why does the second SQL command fails?
WorkS is the sheet I have and it should work

 

[xenou]

sSQL = "INSERT INTO G (U,CM,M,MI,P,PM,R,RPM) Values(WorkS.Cells(2,1),2,3,4,5,6,7,8)"

You will have to concatenate the SQL string from the cell values - otherwise, Access will see this as a very strange command and won't be able to resolve the cells() properties into actual cell values.

Something like this:

Dim sSQL As String
sSQL = "INSERT INTO G (U,CM,M,MI,P,PM,R,RPM) Values("
sSQL = sSQL & [COLOR="RoyalBlue"]WorkS.Cells(2,1).Value[/COLOR]
sSQL = sSQL & ",2,3,4,5,6,7,8)"

In practice, I always "inspect" the final SQL string with a debug.print or msgbox statement. You want the final string value to be a bona fide sql command that you could run "as is" in Access (You might see the point of this right away if you did it with your first attempt - sometimes it looks fine in code because you're partly thinking in VBA yourself, but when you see the result in your message box you realize that what you've created won't run as straight SQL):

Dim sSQL As String
sSQL = "INSERT INTO G (U,CM,M,MI,P,PM,R,RPM) Values("
sSQL = sSQL & WorkS.Cells(2,1).Value
sSQL = sSQL & ",2,3,4,5,6,7,8)"
[COLOR="RoyalBlue"]Debug.Print sSQL[/COLOR] '//output sql string to immediate window for viewing
[COLOR="SeaGreen"]'Or...[/COLOR]
Msgbox sSQL '//show sql string for viewing

 

[gyferlotus]

Thanks for the info xenou you taught me a lot
I really appreciate it