Running make table query from another database

[mblack76]

Hi all,
I am trying to update a table in a database (Lets call that DbNew). I want that when DbNew form is loaded it automatically updates its underlying table (lets call it TblNew) by running a make table query (called "coversheet") that is located in another database (call that DbOld). DbNew and dbOld are all on a network and I dont have total access on DbOld (but I have created the make table query "coversheet in DbOld).
I should also state that DbNew is bound to TblNew and I am not interested in keeping the old table in DbNew. Information is frequently updated in DbOld and all I am interested in is loading the most current info in DbNew so the current table can be deleted upon loading DbNew and a new one created. Here is what I tried doing and I have it in the form load event of DbNew. This however returns an error (Runtime Error 3010. That TblNew already exists)

Dim db As Database
Set db = OpenDatabase("\\network\dbOld.mdb")
db.Execute ("Coversheet")
DoEvents
Set db = Nothing
End Sub

P/S: I am not good at access in anyway I should state.
Thank you for taking the time to read.

 

[Bat17]

why not just link the tables and you will always be current!

Peter

 

[mblack76]

why not just link the tables and you will always be current!

Peter

The table on DbOld is very big and complex and requires an ODBC connection that I cant get. So I am creating a table from a query. I however got the code to run when I deleted the TblNew from DbNew and made the form an unbound form so I guess if there was a way I could delete Tblnew on load and set the record source to TblNew upon 'import' all will be well. I will try the link table option (I have never used it before)
Thank you for the input

 

[Bat17]

If they are both access databases then you don't need ODBC just File>Get External Data>Link Tables... which will bring up a open file dialog box. This will let you select your old db you just need to pick your table then.

If the table is too big/slow to use linked all the time you can still use it to make a subset of data with an update query. I would deletle data and replace it within a table rather than actualy keep deleting the table. Saves on bloat.

Peter

 

[neileg]

I think that you should delete the contents of TblNew and then append the records from DbOld to TblNew, not create a new table.

Edit - beat me to it, Peter.

 

[mblack76]

OK guys, I was wrong somewhere. I am sorry about that. I tried using the link table option but could not find my source table in DbOld. I then went back to DbOld and looked at the tables. Behold, the Table that has the info I am interested in has a sort of globe Icon with a link table arrow on the left. When I mouse over it, it says ODBC;Driver=SQLServer.....When I try to open it in design view, it says TblOld is a linked table with properties that cant be modified.
So in other words, TblOld seems to be a link table created with an ODBC conection.
Hope that provides more info to work with

 

[mblack76]

I think that you should delete the contents of TblNew and then append the records from DbOld to TblNew, not create a new table.

Edit - beat me to it, Peter.

In other words, I should change the make table query to an Append query right? So how do I set it up to delete and replace all entries when I load it?

 

[Bat17]

two queries, one deletes the other fills you can run them both from code to keep things simple.

peter

 

[mblack76]

two queries, one deletes the other fills you can run them both from code to keep things simple.

peter

I think I finally figured it out how to delete the records. I did it using a macro. Now I am trying to use run code to get it to append