Vba access passing the table name with variables (1 Viewer)

[HASAN-1993]

Passing the table name with variables. I have Query. I do it from the Query builder itself, not from Visual Basic Editor. But I want to pass the table name from a variable or from a module. Does anyone have an idea?

 

[HASAN-1993]

I searched and found out that this solution is not possible
The whole idea is that I want Query when opening it, if there is internet, it opens a table, and if not, it opens another, exclusively when opening Query because the program is in another programming language and I do not have access to the codes

 

[Gasman]

You could amend the qdf of the query and replace either a placeholder or the actual name of the table in the query.

 

[HASAN-1993]

You could amend the qdf of the query and replace either a placeholder or the actual name of the table in the query.

Ok, in this case, how can I run the code before opening the query

 

[Gasman]

With VBA?

 

[HASAN-1993]

With VBA?

As I have already explained, I do not have an option to run the Vba code except when opening the query itself because the program is in another programming language, so if I make a function QueryDef, how will I run it when opening the query

 

[Gasman]

Sorry, I am not understanding you.

I do it from the Query builder itself, not from Visual Basic Editor

I took that to mean exactly what it said?

I am unsure where this query is located now?
Why not just hjave two different queries in whatever that program is, and run one depending onthe internet?

Either way, you need someway of making a decision and then acting on it.

 

[HASAN-1993]

Sorry, I am not understanding you.

I took that to mean exactly what it said?

I am unsure where this query is located now?
Why not just hjave two different queries in whatever that program is, and run one depending onthe internet?

Either way, you need someway of making a decision and then acting on it.

Ok. I have a program, the frontend in Vb.net language, and I do not have access, and the backend is ms access, and I have full access.

Now the program is calling a query named Customers. All I want is when the program calls this query, is for the query to check a specific condition. If the condition is true, it displays a table called Customers Local, and if False, it displays a table called Customers Online.

 

[June7]

Table name in query cannot be dynamic.

However, maybe a UNION query could serve.

SELECT Field1, Field2, Field3, "Local" AS Src FROM [Customers Local]
UNION SELECT Field1, Field2, Field3, "Online" FROM [Customers Online];

Now query Customers pulls from UNION with filter on Src field. This will require referencing a custom VBA function or a TempVars variable.
SELECT * FROM qUNION WHERE Src = [TempVars variable or call function];

Or someone should modify vb.net procedure.

 

[HASAN-1993]

Table name in query cannot be dynamic.

However, maybe a UNION query could serve.

SELECT Field1, Field2, Field3, "Local" AS Src FROM [Customers Local]
UNION SELECT Field1, Field2, Field3, "Online" FROM [Customers Online];

Now query Customers pulls from UNION with filter on Src field. This will require referencing a custom VBA function or a TempVars variable.
SELECT * FROM qUNION WHERE Src = [TempVars variable or call function];

Or someone should modify vb.net procedure.

I thought about this solution, but the problem is that the table (customers online) when there is no internet connection, the query will not work at all

 

[June7]

Who wrote the vb.net app? Better talk to them.

 

[Josef P.]

What is this condition used to detect an online status?
Or is the status set externally in a table?

 

[Gasman]

I think your hands are tied here.
Even in vb.net, you would need to make the decision.

 

[HASAN-1993]

What is this condition used to detect an online status?
Or is the status set externally in a table?

I have a module that I made in the past, and I will send it to you later if you want

 

[Josef P.]

And this module (function?) could be used from .net?

 

[HASAN-1993]

And this module (function?) could be used from .net?

I honestly don't know because I only work in vba

 

[Josef P.]

Then you actually have 2 problem areas.
Up to now you concentrated on the table selection and assumed a working condition.

In my opinion, if an Access query is accessed via .net using ACE/JET, a VBA function built into the query will not run. ACE does not know the VBA functions, because in such a case the expression services will not run (no Access instance is running).
@Experts: please correct if I am wrong here.
And if this already doesn't work, there is no need to worry about dynamic table selection in a query.

Another thought:
Could run a windows service that detects the online status and swaps the table in the query when it changes?

 

[HASAN-1993]

Then you actually have 2 problem areas.
Up to now you concentrated on the table selection and assumed a working condition.

In my opinion, if an Access query is accessed via .net using ACE/JET, a VBA function built into the query will not run. ACE does not know the VBA functions, because in such a case the expression services will not run (no Access instance is running).
@Experts: please correct if I am wrong here.
And if this already doesn't work, there is no need to worry about dynamic table selection in a query.

Another thought:
Could run a windows service that detects the online status and swaps the table in the query when it changes?

And how do I make a Windows Service that adjusts the table

 

[Galaxiom]

And how do I make a Windows Service that adjusts the table

You write a program that runs on the computer with the database. It could be done in any language, even VBA in an Office application if that is all you know but it would be better as a stand alone program.
You need to keep it running which is why it was suggested to run it as a service.

 

[June7]

Review https://stackoverflow.com/questions/415409/run-batch-file-as-a-windows-service