USe ODBC to Overwrite Table

[nsohenick]

I am using an export module in our accounting package to export 4 fields into an access table. I had to create my very first ODBC data source. I works ok. The problem I am having is that every time I export the fields it appends to the table and creates duplicates, triplicates and so on.

Is there a way that I can delete all rows when the file is opened for writing, not reading. This simple db will be queried on many times throughout the day.

Thanks for any advice you can give

 

[Calvin]

The trick is going to be with your accounting package, is the export scipted in any way, do you have the ability to modify your SQL statement that is run when exporting the data?
If so, then you might be able to set up an export or sql statement that when run will delete all existing records prior to the insertion of the new data.
Otherwise, Is there a process on the Access side that could possibly be used as a trigger for deleting the data. Maybe two tables, one that your accounting software uses to push data into and the second that your access process uses and retrieves data from the first table and resets everytime it is run.

 

[nsohenick]

I was going through the Access object browser and saw two access vb constant functions that looked like they's do the trick. They are 'acSelectRecords' and 'acDelete'. I could not get any syntax or examples has anyone used these before? I was hoping to use use them like this:

Function IsLoaded

If DatatranferType = acImport then
acSelectRecords
acDelete
End If

End Function

But I need to learn how to use these functions.

Thanks!

 

[nsohenick]

BTW... The Accounting package does not have the ablilty to use SQL statements

 

[Calvin]

Those constancts do not work the way you think they do, they are used within various methods. Genreally you need to use a recordset for manipulating data.

Why don't you explain the process that you have in mind for access and maybe someone can point you in the right direction.

 

[nsohenick]

I am using a batch file (Scheduled through windows scheduler) to invoke an export out of my accounting package. The export appends to and odbc table in Access. I want to simply overwrite, but I do not have that option with the package. I can write perform logic to run before the export begins, but I need to Providex.

It seems to me that I need to write some vb code in the modules section to check when the form is loaded whether it is opened for write or opened for reading. If it is open for writing, then DoCmd.acSelectAllRecords, then DoCmd.acDelete. I will really need some help in the vb part. I am a beginner.

Thanks for your help.

 

[Big Otis]

Since you are already using a batch file, why not add a line at the top of the batch file to open the access db and execute a macro that deletes the records in your target table. Make sure the last line of the macro closes access.

 

[nsohenick]

My macro in the Access db is not working. Maybe you can tell me what I'm doing wrong. It doesn't seem to run at all. It is still appending.

As for adding a line of code to the batch file. I'll need help with that too. I created the batch file from a help file from within the accounting system.

Here is my code:

Function IsLoaded()
stDocName = "JC1_JobMaster1"
DoCmd.OpenTable stDocName, acNormal, acEdit
DoCmd.RunCommand acCmdRecordsGoToFirst
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.acCmdDeleteRows
End Function

(I still need to add a line to check the transfertype or read type, but even this way its not working. All the records are still there.)

Thanks a lot

 

[Big Otis]

Note: do all your testing on a copy of your production file .....dont want to accidentally whack the wrong thing ....


If your macro is trying to execute the function you listed, be sure the Macro is trying to RunCode .... try this to remove the records ..

Function ClearRecords
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("delete * from table")
    DoCmd.SetWarnings True
    DoCmd.Quit      
End Function

As for editing the batch file, you would first look in the Access help for the command line specs. But,basically your added line would include the following in this order (don't forget to double quotes around the path if it includes spaces)

"Full path to Access.exe" "Full path to target mdb" /x MacroNAme

 

[nsohenick]

Thanks. I used this to clear the records and it works.

Function ClearRecords()

stDocName = "JC1_JobMaster1"
DoCmd.OpenTable stDocName, acNormal, acEdit
DoCmd.RunCommand acCmdRecordsGoToFirst
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete

End Function

I created a macro to run this code, which also works when I run it manually. How can I get this macro to run automatically when the db is opened for importing?

 

[Big Otis]

Can you post your batch file so i can see what it's doing? Now i've gotten a little confused on what you are wanting to do.
I interpreted your desire to be

1. Delete records in table before import.
2. Import records.

Did i miss it?

 

[nsohenick]

No you didn't miss anything. I am better at using the built in GUI in the accounting package to choose the fields to export than actual programming. So I went back to my original plan since I already had a working batch file to export the data.

As for the "Clearing line" in the batch file, I keep getting this error: "The system cannot find the specified path".

 

[Big Otis]

I may have misled you, but double check the name of the EXE file for Access. It probably should be MSAccess.exe ....At least that's what both versions i have say ....Oooops.

 

[nsohenick]

Duh...

It still doesn't work somehow. I made a copy of the database on c: and I created a batch file just to clear the records. Here is the line in the file:

"c:\Progra~1\Micros~1\Office10\MSAccess.exe" "c:\db1.mdb" /x Clear

I also updated my code to set warnings to false. Here is that code:

Function ClearRecords()

stDocName = "JC1_JobMaster1"
DoCmd.SetWarnings False
DoCmd.OpenTable stDocName, acNormal, acEdit
DoCmd.RunCommand acCmdRecordsGoToFirst
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.SetWarnings True
DoCmd.Quit

End Function

When I run my macro from within Access it runs fine.

Any other suggestions?

 

[Big Otis]

Mine worked with when i typed the full path name to access with the double quotes and removed the quotes from around the path to the db because it had no spaces in it .....

 

[nsohenick]

Mine still doesn't work, but we're getting off the Access subject... Could you offer anymore advice on batch files? The Access part is working great!

Thanks!

 

[nsohenick]

I got it working. Thanks again.

 

[Big Otis]

Good,glad you got it going. As for batch files, know em, love em, use em ....

You can do a search on google and find lots of sites to read up on syntax, usages , etc.

I use them to kick off Windows Script files to process and move files, load and retrieve data from dbs, send status messages and emails , and probaly some other things i have forgotten about.