Moving table to existing backend using vba (1 Viewer)

[Design by Sue]

I have a client that is using a split database. I am working on an update to the program and need to transfer a table to the backend that has the correct structure and information included in it. My thoughts are to make a one time use program that transfers the table to the backend. I have seen DoCmd.TransferDatabase and DoCmd.CopyObject as possible ways to go. Can someone please give me a clue as to the best way to do this?

Sue

 

[Trevor G]

Either the Transfer or Copy object will do what you want.

I would have a preference to use either based on the objectives.

 

[Solo712]

I have a client that is using a split database. I am working on an update to the program and need to transfer a table to the backend that has the correct structure and information included in it. My thoughts are to make a one time use program that transfers the table to the backend. I have seen DoCmd.TransferDatabase and DoCmd.CopyObject as possible ways to go. Can someone please give me a clue as to the best way to do this?

Sue

FYI, you can export the table to the BE without any coding.

Best,
Jiri

 

[Design by Sue]

Thanks Trevor. Can you give a clue to the difference? More detail:

There is an existing frontend that the everyday user works with. This creates reports that are used as sales sheets. Every week there is an update of the database info from an excel file, which I have created a 2nd program (Updater) to handle. With the creation of this new program, there is a table that needs to be stored in the backend for this 2nd program (so it is accessible to anyone that runs this updater program). I am looking for the best method to add this table to the backend. I am creating a 3rd program which is to be run once and will move the table to the backend (I will have a copy of the table in this 3rd program) as well as update one of the existing tables to add a field. This needs to work on the click of a button - I can't have the user working directly with the tables.

Additional thoughts?

 

[Cronk]

When you say "2nd program", do you mean a second Access database? If so, why not put the functionality into the front end?

I assume that the new table that is to be added to the backend is not one being added every week. If it is to be a once off procedure, why not just do it manually? Or are you remote from the site and can't open the backend?

 

[Design by Sue]

Cronk - Correct it is not added over and over, it is a one time event. Would love to do it manually but can't because yes I cannot go to the site and do this.

 

[Cronk]

I maintain a couple of remote databases and have at times need to add new tables/fields to the backend.

I prefer to add the functionality to do this in the front end, rather than a separate database confusing to the user. I use a boolean field in a system table in the front end which indicates whether the update has been done on opening the database, so that the update is not repeatedly applied.

I prefer to use DDL to add/alter/delete tables. You can search for further information.

A sample to give you a start is

Set dbData = DBEngine().OpenDatabase(strPath, True)
dbData.Execute "CREATE TABLE tblDeletedPayments ( PaymentID Long, BookingID Long, SubscriptionID Long, " & _
   "SubscriptionAmount Long, ChequeDrawer Text (255), Amount Currency, Rounding Currency, OtherPayment Bit, DateReceived Date, " & _
   "ChequeNo Text (50), PaymentMethod Long,  ReceiptNo Long, Bank Text (50), Branch Text (50), ToBeBanked Bit, " & _
   "Banked Bit, DateBanked Date, CreditReferenceID Long, TimeStampX Date, AmendTimeStamp Date, ReceivedFrom Text (255), " & _
   "CardType Long, AccomAmount Currency, OtherAmount Currency, DeleteTime Date)"

In the above, strPath contains the full path of the backend as seen by your users. The code creates a new table. Then link to the database containing the data to be imported and use an append query to add your data.

 

[Design by Sue]

Cronk - thank you for your advise. I will be working on this next week and will take your suggestion and add the code to the frontend, though I need to transfer a table (already has info in it) rather than create one.

Sue

 

[Design by Sue]

Cronk - if you are there, I would appreciate a little advise. You stated:

I prefer to add the functionality to do this in the front end, rather than a separate database confusing to the user. I use a boolean field in a system table in the front end which indicates whether the update has been done on opening the database, so that the update is not repeatedly applied.

I am trying to figure out how to do this. From what I understand you have a table in the frontend with a yes no field indicating the update was completed. I think this could be an issue if there is more than one copy of the frontend of a program, the updated info may not be the same from one copy to the next so I don't understand how that would work. Do you have any other suggestions on how to avoid rerunning the code if it is in the frontend rather than another separate "RunOnce" application?

Thanks

 

[Cronk]

The update could easily be made to test if the update has already been performed.

I gave you my preference, not a unique solution.

 

[Design by Sue]

Thanks for your reply - I found a way to check for the existence of a table in the backend that seems to work correctly so I will use that in an If statement.

Sue