Code to Import Text or Excel file 2

[shelby55]

Hi

I am using Access 2002 and a newbie at VBA.

I have a database that will be updated monthly as new data is available. But I want whatever data is in the file being updated to overwrite what is in the table.

In other words, if I am uploading Apr to Sep 2010 data then I want the Apr to Aug data in the table to be overwritten with the newer data.

What would my code look like? Thanks.

 

[MaxieMax]

Shelby,

Where I used db.execute strsql I had set up s string strsql which cleared down a table i was about to load the data to.

if you comment out the strsql and db.execute lines but leave the set db=currentdb then it should work for you....

to comment out place a ' at the beginning of the line like I did with the explainations in the script I provided above.

Cheers
M

 

[shelby55]

Hi MaxieMax

Thanks very much, this worked for that part of the code. Now the compiler objects to db.openrecordset and the error is "method or data member not found"......

 

[MaxieMax]

Shelby....

You need to keep the line

set db = currentdb

you set the dimension with the dim line... but you then still have to asign it to your database with the db line.

however you can comment out the below as well

strsql = "select name from MSysobjects where name like '*_importErrors'"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset)
Do While Not rs.EOF
DoCmd.DeleteObject acTable, rs!Name
rs.MoveNext
Loop
rs.Close
Set rs = db.OpenRecordset("Filelog", dbOpenTable)
With rs
.AddNew
rs!Filename = varFile
rs!load_Date = Now()
.Update
End With
rs.Close
Set rs = Nothing

this section deletes error tables and updates a filelog table i use to keep a record of the files i have imported so that they can't be imported more than once

see how you go

M

 

[shelby55]

Hi

Thanks. I removed all of that but it now balks at the linke of db.close (again, it doesn't have that as an option for the db, only count or refresh).

The error message is "compile error: method or data member not found".

 

[MaxieMax]

Sorry should have said remove that and any reference to set db = nothing as well

Cheers
Max

 

[shelby55]

Hi Max

Thank you so much for your infinite patience it now works! Now I just have to play with it to be able to have the new data run an update query.

Have a great day!!

 

[MaxieMax]

No worries feel free to drop me a line if you need more help!

M