Vba code to switch link of specific table to folder based on variable!

[gangel]

Hey everyone,



So my situation is this.. (which im sure im going to be told is a stupid idea... but i think it's what i can actually afford..)

- My PC has all data and tables. (Split, but located on C: drive)
- This DB is also linked to 3 tables (Customers, Claims and Scripts) which are located on dropbox in a folder according to a Company name.

eg.
Dropbox/WB
Dropbox/CA
Dropbox/PP

The 2 digit ID tags are acompanies name./

Now what happens is each folder has LOGIC.accdb in it, which i connect to.

I first check to see if the .laccdb file exists. If it doesn't, then i copy some data from that accdb file.

What i want:

On PC1 on a form I have:

WB - BtnImport
CA - BtnImport
PP - BtnImport


So when i press the BtnImport for WB the linked tables for the 3 mentioned above, switch the Folder from where it is to "C:\Dropbox\" & CompanyID & "\"
Which results in C:\Dropbox\WB\ being the target now, and i can use the data.

Then when i press the CA import it then disconnects from WB and connects to CA...

I've seen a bunch of codes for checking tables exist in the link etc etc, but i didn't know how to manipulate the code for my above purpose.

Thanks for any ideas, or even a push and shove in the right direction.

Gangel.

PS. I do understand that Dropbox is not a server, it is for backups, but the important part here is that it is an easy way to share the data and as long as we don't connect and use the data at the same time, (Which i'm using checks for .laccdb) then i can use that data for the 0.5 seconds it takes to run my query and disconnect.

[ItsMe]

I think you are going to be fighting corruption issues. If the internet connection is lost/broken, either on your end or your client's, you are going to have data integrity issues and also orphaned .laccdb files.

I do not know how complex your application needs to be, maybe you just need a simple query for your clients to append records to two or three tables. I am not mentioning this because I think a simple access db will work on dropbox. I mention it because you might be able to manage the situation using a service like Azure SQL Database as a backend.

Using SQL Server as a backend makes things a little more complicated than a desktop DB. However, what you are describing in post #1 is managing connection strings. So, might as well be managing connection strings to a service that is designed for remote connections over the internet.

Otherwise, you might consider your clients emailing a zipped Access file, a spreadsheet, a text file, etc.. They could have an Access file that resides on their PC, when they are done with their inputs, they hit an email button and the recent updates are emailed to you.

[gangel]

Hey ItsMe,
Thanks again for the ideas and input.

I was more than happy with a export data situation but my issue lies with this:

Server has 30,000 customers on it (5000 current, others are historical)
Each company has around 20-40 customers
Therefore when the Client adds their customers (CustomerID 1 to 40) then sends me their export, i have no link for easy input to my data.

My real only need for connecting to their DB is to connect to their DB and add "ServerCustomerID" as a field to their table.

Then when they export they will export customers that i have connected to my DB, and then if a new customer from them comes on board then i have a query looking for "ServerCustomerID is null" and i can import that customer (eg customer 41) to my server (As customer 30,001) and then attach that ID to the company DB and form then on i have an easy match

I hope that makes sense and you can see my "issue".

Other than that i was just going to CSV or excel export, but i came up with my customer issue

[ItsMe]

The simplest approach would be to use a GUID as a Key. What you can do is create a new table in Design View. Add a primary key with type Autonumber. With the Autonumber type selected/highlighted, look at the properties under the General tab. In the Field Size, change Long Integer to Replication. I believe it is replication. Anyway, the idea is that instead of an integer, you create a GUID.

GUID's are not the most intuitive. Sometimes they are a String, sometimes a GUID, and sometimes they are something else ie uniqueID.

Maybe you can spend a day or two playing around with GUID's. The difficult part is using them in queries and having one RDBMS understand a GUID generated by another RDBMS. Also, be careful with timestamps generated from an outside source. You will want them, just make sure your queries understand their origin. You do not want to compare a timestamp from an outside system with your system's clock.

One search came up with the following ... The post talks about generating a GUID and I do not believe you will have a need for that. However, there is some code for converting a GUID into a string. Also, there should be some code around here that you can include a GUID Type directly in Access SQL as well as a custom VBA function to do a string conversion.
https://www.accessforums.net/forms/w...tml#post272333