Variable link to a path folder (1 Viewer)

Andre B

New member
Local time
Today, 09:26
Joined
Dec 31, 2019
Messages
6
Hi,

I've created an SQL code in VBA to update some tables on a server.
Now I want have the path of the database on a form, so the user can edit the path if needed.

How can I use a variable link for the path in this code?

thanks in advance,

rg,
Andre

Code:
DoCmd.RunSQL "INSERT INTO  [;database=C:\Users\nlbruand\Documents\Braakman\HOB server\IBS-data.mdb].orders ( OrderNr, MedewerkerNr, KlantNr, Onderwerp, Orderdatum, " & _
"OrderStatus, Notities, ContactpersoonNr, Lokatie )" & _
"SELECT orders_lokaal.OrderNr, orders_lokaal.MedewerkerNr, orders_lokaal.KlantNr, orders_lokaal.Onderwerp, orders_lokaal.Orderdatum, orders_lokaal.OrderStatus, " & _
"orders_lokaal.Notities, orders_lokaal.ContactpersoonNr, orders_lokaal.Lokatie " & _
"FROM orders_lokaal;"
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:26
Joined
Jan 23, 2006
Messages
15,364
?? Why would the user need to edit the path to the database?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 28, 2001
Messages
27,001
Divide and conquer. Break the problem into parts.

You have an SQL string already defined, right? Instead of using a literal string, break this process into parts.

First part: Concatenate the path into the string. I'm using "PathControl" to represent the name of a control on a form where you put the path. You use any name you need. Doing it in parts lets you examine the string while debugging so that you can see what was being attempted.

Code:
TheSQLString = "INSERT INTO  [;database=" & Me.PathControl & "].orders " &_
( OrderNr, MedewerkerNr, KlantNr, Onderwerp, Orderdatum, " & _
"OrderStatus, Notities, ContactpersoonNr, Lokatie )" & _
"SELECT orders_lokaal.OrderNr, orders_lokaal.MedewerkerNr, orders_lokaal.KlantNr, orders_lokaal.Onderwerp, orders_lokaal.Orderdatum, orders_lokaal.OrderStatus, " & _
"orders_lokaal.Notities, orders_lokaal.ContactpersoonNr, orders_lokaal.Lokatie " & _
"FROM orders_lokaal;"

Second step:

Code:
DoCmd.RunSQL TheSQLString

EDIT: Jdraw's question is valid. There is an implication that you have more than one DB accessible this way, and that each has adequate structural similarities that this process would work.

Which means you aren't sharing this information completely across multiple users. It ALSO means (or at least implies) that they have permissions necessary to update a database this way.

I'm not at all saying such a setup is impossible. It might even conceivably be the right answer. But it IS suspicious because it seems to be violating some of the more common "best practices" that we usually preach here.

What are you doing with multiple distributed databases such that this action is even possible?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:26
Joined
Oct 29, 2018
Messages
21,358
Hi André. Belated welcome to AWF!


Perhaps this other thread would provide some background information to maybe help answer some of the questions asked above.


Cheers!
 

Andre B

New member
Local time
Today, 09:26
Joined
Dec 31, 2019
Messages
6
Thanks, it works properly now.

A little background;
I'm helping a friend of my to make some changes in his database.
He uses a laptop if he is not in the office and when he is back, he has to synchronize the data of the laptop to the database on the server.

So, I don't have to distribute the database, but it's now on my laptop and then we can easily change the path on the form without changing the code on the background when I copied the program to the laptop.

rg,

Andre
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:26
Joined
Feb 28, 2001
Messages
27,001
I see. You are doing this to work around the fact that Microsoft in their ginormous wisdom got rid of replication and synchronization. Makes sense in that context.
 

Users who are viewing this thread

Top Bottom