deleteing and importing

[DirtDawg]

Hello,

I have two questions.
1. how to make a button so that it will browse to an excel file then automaticly import into the proper table?

2. Make a button so that it will delete all records from a table?

Thanks

 

[shyamsundar]

Hi Hunnicutt,

Question 1:

Try ..Docmd.transferspreadsheet

however, you have to specify the table name into which you are importing and excel filename which u r importing. For user freindly browsing the excel file, you have to create a some forms insert some objects for this and u have to write a code for this. If you want let me try and send it to u.


Question 2:

Write a query to delete everything in the table as...
create new query and type the following in query view

delete [yourtablename.*] from yourtablename

and save it (eg,,qryDEL). Now create a new form and insert a button thru button wizard and select runquery from miscellaneous and select the above query name(qryDEL), to delete. Hope I am clear.

Best

Shyam

 

[DirtDawg]

shyamsundar,

If you could send me the code that would be great. I already have a table sent up to import the excel file into. I am trying to figure out the code part now. I will have to import 5 spreadsheets into one table. my email is sean@miscjapan.co.jp.

 

[jbates]

1. The TransferSreadsheet method should do the trick. It is much safer to first import the worksheet into its own table. then you want to append the records to an existing table.

2. Use the DELETE DML avaliable in Access. in the next example, I will delete all the records in my customer table named tblCustomer:

DELETE *
FROM tblCustomer;


Good luck

Jamie

 

[DirtDawg]

I have gooten the browse button to work. but when I try and do the import I am getting errors. like this one

Run-time error 3011

Msjet database engine could not find object '0'
This is the code I am using

Private Sub Command3_Click()

Dim strfile As String
strfile = ("" & xls_file & "&quot

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "equipment", strfile, True, Sheet1!