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

I meant -

Dim db As Database
Dim rs As Recordset

cheers

M

 

[shelby55]

Hi Max

I thought of that and removed the DAO but it's still not working. Very strange.....

 

[shelby55]

Hi

Also, I don't think it makes a difference but I put the code on a button on a form for the "on click" event.

Thanks.

 

[PHV]

Why would that be a problem
You have to add a reference to the Microsoft DAO x.y Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi Max

Thanks very much...adding that reference fixed it! But now it is balking at fDialog as Office.FileDialog.

I'm assuming there might be something missing in references as well? Can you please advise what other items I should have selected under references?

Thanks so much!

 

[MaxieMax]

Shelby

1. On the View menu, click Code to open the form's module in the Visual Basic Editor.
2. On the Tools menu, click References.
3. Click to select the Microsoft Office 10.0 Object Library check box.
4. Click OK to close the References dialog box.

Cheers

M

 

[PHV]

Reference the Microsoft Office x.y Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks to you both...but I've done that (from the previous issue) and still it is not liking the fDialog as Office.FileDialog dim statement.

The references I have selected:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

Thanks.

 

[PHV]

What about Reference the Microsoft Office 11.0 Object Library ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MaxieMax]

The Only Ones I am using that your not is

microsoft Excel 12.0 object Library
Microsoft office XP web components
microsoft visual basic for applications extensibility 5.3

and it all works fine in my version (obviously) otherwise i wouldn't have given you the script lol.....

strange that if you have these references that it doesn't like the DAO element of the dimension

add these in and see how you go

 

[shelby55]

Hi

Thanks MaxieMax..but still not working. I added in all you suggested.

PHV..not sure what your comments mean.

 

[PHV]

PHV..not sure what your comments mean
IMO, you have to add a reference to the Microsoft Office 11.0 Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MaxieMax]

Shelby,

try checking out this -

http://support.microsoft.com/kb/279508

M

 

[shelby55]

Thanks PHV...yes I do have that flagged in my references (see my list in my post at 8:52)

 

[MaxieMax]

could always try using

strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

never used it myself but you may be able to get it to work.

google - ahtCommonFileOpenSave for more info

Cheers
M

 

[PHV]

see my list in my post at 8:52
You don't have any reference the office library ...

 

[shelby55]

Hi

Sorry PHV...the descriptors all look alike! I added the MS Office Library and now it accepts the FDialog dim statement.

Of course, it has found something else it doesn't like:
db.Execute strsql

 

[PHV]

it doesn't like
???
Which error message ?
What is the value of strsql ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Sorry it also lists it as a compile error ...method or data member not found. If I select db. I only get the selections of count or refresh and not Execute.

 

[PHV]

Well, why not post your ACTUAL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886