Importing data to Access

  • Thread starter Thread starter excel_hari
  • Start date Start date
E

excel_hari

Hi,

I have 2 needs:-

a) I want to import excel data to an existing table in my DB. While
doing so, I want to delete the existing records in the table. If I use
the following code (Pinched from the NewsGroup)

Sub TransferData()

Dim MyPath As String
Dim MyFile As String

MyPath = "C:\Hari\Assorted stuff\For Mohan\"
MyFile = "Copy of April06Fnubextract .xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"OSRM_Table", MyPath & MyFile, True, "Report 1!"

End Sub

Then, the new data gets stacked below existing data. How to avoid this
problem?

b) Suppose I want to import data from Excel in to a table which doesnt
exist in my DB, then how to do it. Basically I want a code which culd
mimic the Getexternal data in File menu. Is this possible?

Also, is it possible to see the code behind a macro in Access. Iam new
to Access and work on excel only and its strange that one cannot record
macros the way one does in Excel. In excel one could see the code for
recorded macros and customize accordingly.

Regards,
HP
India
 
a) Add this line:
Currentdb.Execute "Delete * from [Report 1]
just before the DoCmd. It will delete all the records existing in the
table.

b) When you use TransferSpreadsheet to import, if the table name you specify
does not exist, it will be created. If the table does exist, the data will
be appended to the existing table.

c) Access macros are not VBA code like in Excel. However you can
right-click on the macro in the database window and choose SaveAs... and in
the As... box, choose Module. This will save the macro actions into
equivalent VBA code.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger,

Thanks for your tip regarding saving a macro within a module and a
table getting created if it doesnt exists in Spreadsheet Transfer
method.

I have one follow up doubt in Deleting persent records from table. It
could be possible that I might not have the Table itself in the
database. How do I check for whether a table exists or not. I want to
write some conditional code (boolean condition) which takes action
based on whether table exists or not?

Something like
if Table1 exists then CurrentDb.Execute "Delete * from OSRM_Table "

How to put it correctly?

Please guide me.

Regards,
HP
India
 
Back
Top