Check if table exists in backend (1 Viewer)

[Rachael]

Hi,

I was wondering if someone could tell me the easiest (and/or best) method to check if a table exists in a backend database.

What I am wanting to do is add to the OnOpen procedure of my splash screen in the frontend a routine that checks if a particular table exists in the backend and if it doesn't uses the Docmd.transferdatabase method to copy it from the frontend to the backend. I will have a empty table in the frontend for copying purposes and a link set up ready for when the table gets copied. Obviously once the table is copied I don't want it to happen again as it will keep writing over peoples data (I am distributing this database). I can do the copying bit but don't know how to do the checking bit. Am I on the right track or is there another way?

Thanks

Rachael

 

[bradcccs]

G'day Mate

I am not too sure on the full correctness of my methods, but what I do is:

If I have not included version stores in the Backend originally ->

I have a table within the frontend that stores the current version number of the backend. Thus, if Backend V1.0 was in operation, my Tbl_VersionBE would store "1".

Thus, when I distribute a new frontend that will update / add tables to a backend via the procedures we discussed, I base it on the value in the frontend table.

Logic operations:

If Backend Version (Tbl_VersionBE) < Current Version (ie: old version) Then do update procedures to Backend (transferdatabase etc)
Update the BE version stored in Tbl_VersionBE (located in frontend)
Thus in further activations of frontend, the version number is correct, and the transferdatabase operations are not executed.

The only thing you have to remember to do, is distribute the Frontend with the Old Version number initially that will update on the first operation.

I actually now store the BE version number in the backend, but as you do not currently have the BE table in the BE, you will experience the same table exists / doesn't exist problem.

Hope I haven't completely confused you.

If you need clarification, let me know.

Brad.


** edit **

Removed "=" in logic operations. Only update if OLD version exists - Sorry

 

[Rachael]

Hi Brad, thanks for your reply.

Where do I put this stuff, in the OnOpen of my splash screen?

This method seems heaps easier than what I was trying to do.

Would it be too much trouble to ask for the code in a bit more detail? I would like to be sure I'm doing the right thing!

Thank you lots and lots

Rachael

 

[bradcccs]

Rachael,

Sorry about the delay in getting back to you.

I have knocked up a mini demo of the process I use. I have not set the splashscreen to auto-open, so you will have to activate it manually.

(if Acc2000 is not suitable, let me know and I will convert it)

I hope this is of some assistance to you.

Brad.

(I can see a bottle of wine coming my way )

 

[Rachael]

Brad, thanks, you legend!, email me your address, we might be neighbors!! Do you prefer red or white, sweet or dry?

Cheers, Rachael