Lost In Access Dilemma

[CurtR]

Hi, everyone.
Being at the bottom of the food chain here,and being that nobody has a clue how to.
I was given the task of figuring a way to automate the following.
Open an Access Database. Delete an existing table. Run an existing macro that recreates the table, and then delete any ( normaly 2 rows) of blank data from the new table.
Lastly close Access.

The table that is deleted and re-created is always named the same thing.
I do not have clue one where to start with this.
Any help on part of or all this would be greatly Appreciated.

Thanks Curt

 

[jmacmicking]

Assuming you just need the data on the table deleted you could use a macro to run delete query without a WHERE clause to delete the table, run your macro, run a delete query with a WHERE isnull([your fieldname]) clause and then exit Access using a RunCommand(Exit) action in the macro.

Creating a Delete query is just like creating a regular query. In fact, you would normally create a standard query in Design view that selects just the data you want deleted and then open the Query menu and select Delete. You'll see a new line on the query labeled Delete; select WHERE for any item that determines the data you want deleted and then drag down the * from the table you want rows deleted from and set the Delete option to FROM.

Keep in mind this is written from the viewpoint of Access 2000; the features are all there in earlier versions but the names are different. If you have trouble in an earlier version of access let me know what version you are using and I'll see if I can give you the exact commands.

 

[CurtR]

jmacmicking
Thanks for responding.
Please allow me to explain this fully.
We have a database (dBase 3). which automaticly picks up data every 60secs.
we also have an MS Access database with several Queries based on a table Manual.mdb

All of the crystal reports are based on the MS Access database. So to bring it current everytime some one wants a report. I get a phone call to do the following.
I open the dBase 3 database manual.dbf with Excel, and do a Save AS Manual1.dbf
I then open the MS Access database and Delete the Manual.mdb table.
I then run the Macro which imports the Manual1.dbf creates an index replacing the manaul.mdb table that I deleted with one with current data.
Due to an error there is always 2 blank rows of data that must be deleted before crystal will run correctly. So I delete any and all blank rows from the database.
Close it, and then call the person back to say OK run your reports!
So I do all that everytime someone wants a report.
I just figured that there should be some way of getting it done with a push of a button from either Excel, Access or even VB.

 

[MichaelRed]

Being a Big ANTI fan of Excel AND not rembering much about the old dbase/FoxPro stuffffffffff, I'm not mush help here - BUT .........

Much of what your'e doing is just not very necessacy. If you can "Import" the dbf file, you can link to it. If you can link to the original dbf file, you can run queries aginst it. If you can run queries aginst it, they can be the recordsource(s) for your reports.

So, Unless I'm missing something, you can just link to the original dbf file in Ms. Access and generate the query / queries to get the records necessary for the reports with NO manula intervention (or even phone call).

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jmacmicking]

It would probably be easier to link to the DBase database directly inside of Access; I know you can like to tables in DBase III using Access 2000. Since the table(s) is/are linked Access will automatically stay synchronized with the original DBase data. If you need the data in a different format you can run queries on the linked table(s) and then base the other queries on those results. How easy this would be to implement would depend on how much processing you need to do on the data in the DBase database before it's acceptable.

If for some reason this isn't an option let me know which version of Access, Excel and VB you have and I'll see if I can give you more specific pointers. There isn't anything built into any of the programs you listed that can do exactly what you want but there are plenty of ways to program them to do so. If you're not familar with Access, Visual Basic, and/or VBA for Excel though they may be too complicated to implement.

Also; just out of curiousity--is there any reason you go into Excel and do a Save As instead of just copying the file and renaming the copy?

 

[CurtR]

I am going have to check as to why this was set up this way to start with by our current DBA there had to be reason for him to make it the way it is. I hope ! Anyway. I guess that when I was given this I was shown to delete the Manual1.dbf ( the copy) and then copy and rename the Original file. It just seemed to be just as simple to just open the original and do a save as and overwrite the old Manual1.dbf with the new file.
We are still running Office 97.
I was going to do the linking as you suggested as that was my first thought. But then I got into the Queries, and the other tables in this database. And this thing is very complex in the way the tables and queries are brought together, Linked, Joined,formulated, and I think twisted as well : )
I was just hoping to be able to write / record a quick bit of code to do this.
Thanks
Curt

 

[twb123]

I know this post is a little late -- I just joined this forum, and am finding it very helpful.

I'd just do this with a macro, if the linking won't work:
1) Delete Object (whatever the table is); then
2) Transfer Database (there is an option that allows you to define the database to be transferred as dBase III, IV or 5).
Just do this in pull-down menus in Macro Design view.

Insofar as deleting the last 2 lines of (blank) data, why do you necessarily need to do that? Those lines will just be ignored in your queries. But if you really do need to do that, just write a little bit of code to search for a blank key field and delete any such records. If you do that, I'd also run the macro from that module, so that you only have to push the one button...

Good luck!! ;-)