Upload Excel to Access

[Guest]

Hello.
I have an xls file that i want to append to an Access table that has an
autonumber field.
How can i upload this information from Excel to Access?

 

[Guest]

Presuming that your Excel table is the same structure as your Access table,
you should be able to append the Excel data to your Access table like so:

1) Open both your Excel worksheet and your Access database & table you wish
to append
2) Select and copy the Excel data (no headers).
3) Switch to Access, and while viewing the table you wish to update, select
"Edit, Paste Append" from the menu.

The copied rows/records will be appended and the autonumbered field will be
updated appropriately (overwriting any values that might have been in the
related Excel data).

-Glenn Ray
MOS Master

 

[Guest]

Ok, but i'd like to do it programatically.

Presuming that your Excel table is the same structure as your Access table,
you should be able to append the Excel data to your Access table like so:

1) Open both your Excel worksheet and your Access database & table you wish
to append
2) Select and copy the Excel data (no headers).
3) Switch to Access, and while viewing the table you wish to update, select
"Edit, Paste Append" from the menu.

The copied rows/records will be appended and the autonumbered field will be
updated appropriately (overwriting any values that might have been in the
related Excel data).

-Glenn Ray
MOS Master

 

[Guest]

Programmatically is tricky. Unless you are familliar with ADODB and
recordsets then you will be in way over your head. This is not a project for
the Newbie. That having been said if you are ok with ADO then with a little
searching on the web you should be able to get yourself started. After that
we would be more than happy to help you with any specific problems you might
have...

HTH

 

[Jamie Collins]

Programmatically is tricky. Unless you are familliar with ADODB and
recordsets

'Recordsets' may be a red herring here e.g.

Sub no_recordset_needed()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
con.Execute _
"INSERT INTO MyTable (data_col)" & _
" SELECT F1 AS data_col FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\Tempo\db.xls;]" & _
".[Sheet1$B2:E65535];"
End Sub

Jamie.

--

 

[Markonni]

Hi!

There is simpler way to do this. You can make a link from Access/tables view
to the Excel file. Then you can work with that linked file as with any other
table that you have in Access.

Make a right click on the blank space while you are in the Tables view in
Access and choose Link tables.

Then in the field files of type choose xls type of file and select your file
on the disk.

I hope that you will manage to go further alone. If not just ask.

Regards,
Markonni

Programmatically is tricky. Unless you are familliar with ADODB and
recordsets

'Recordsets' may be a red herring here e.g.

Sub no_recordset_needed()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
con.Execute _
"INSERT INTO MyTable (data_col)" & _
" SELECT F1 AS data_col FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\Tempo\db.xls;]" & _
".[Sheet1$B2:E65535];"
End Sub

Jamie.

 

[Jamie Collins]

There is simpler way to do this. You can make a link from Access/tables view
to the Excel file.

The OP posted in .excel.programming then said, "i'd like to do it
programatically", so how about posting some *Excel code* to create the
linked table. Your approach is only simpler if operating on the same
Excel file each time.

Jamie.

--