Results 1 to 2 of 2
2002-06-21, 02:06 #1
- Join Date
- Jan 2002
- Thanked 0 Times in 0 Posts
Create Table In A Backend DB (Access 2000 and 97)
I need to know how can I go about creating new tables via code in a linked backend DB. My problem stems from the fact that I have created a FrontEnd and Backend program that is now being deployed to variuos site that we have. The main problem is that each of these site place the backend in a different path, which is ok because no two enviorments are the same. However when I'm ask to make updates which includes creating new table in the backend, it becomes a huge chore. The last thing I want to do is create a new backend and have them migrate the current information (I can hear the complaints). So, I was wondering if there is a way to create the new table (with field of course) via code that when the new Frontend is deployed and the links are established, it looks to see if the new table is in the Backend and if it isn't it creates it and if it does find it it does not, a sort of run once code. Just in case your wondering how do they link the backend to the new frontend when all of the enviorment are different, the answer is the code I have in the Frontend looks to see if it find the linked table if not it warns you and it prompts you to locate it.
If anyone can help me with this I'll realy appreciate it, if anyone has a better idea I'll be glad to try it.
2002-06-21, 07:05 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Create Table In A Backend DB (Access 2000 and 97)
If you want to copy an existing table from the current database to another database, use the TransferDatabase method of the DoCmd object.
If you want to create a table from scratch, you can use DAO to do this. In Access 97, this is the default data model; in Access 2000, make sure the you have set a reference to the Microsoft DAO x.x Object Library (open any module and select Tools/References...).
In <!post=post 147751, 147751>post 147751<!/post>, you will find an example of creating a table using DAO in the current database. To create it in another database, add the following lines to the beginning of the code there:
<font color=blue>Dim wsp As DAO.Workspace
Set wsp = DAO.DBEngine.Workspaces(0)</font color=blue>
and replace Set dbs = CurrentDb by
<font color=blue>Set dbs = wsp.OpenDatabase("pathdatabase.mdb")</font color=blue>
Substitute the correct path and database name.
At the end of the code, add
<font color=blue>Set wsp = Nothing</font color=blue>
to clean up.