alter SQL in VBA to open query based on different tables

[Ziggy1]

I tried searching but I can't put it into words to get the results I want...

I have many linked tables that I can link to but can't edit. Each table is duplicated for various locations and named as such.


Eg Product

tblProd_1
tblProd_2
tblProd_3
tblProd_4


the numbers are for 4 different locatgions, but the table structure is the same.

I don't do a lot of work with SQL, I ussually just build the queries in design, but it would be easier if I could use a variable to alter the SQL for the table I want to access. This is just a simple example of twhat the table might look like....

SELECT tblProd_1.ClientCode, tblProd_1.ProductCode, tblProd_1.Description1
FROM tblProd_1;

Could someone show me the VBA syntax for just opening the table, I plan to use an option group (for Variable) on a form to choose the various locations for the table to run. I am comfortable with VBA so once I see the syntax. You can Hard code the variable, I should be able to figure it out after that.

The object is not to have to create 4 queries for the same thing but only one in SQL and be able to alter the SQL to choose one of the 4.

hope it makes sense


Thanks

 

[KeithG]

Why do you not want to use Four seperate queries?

 

[voskouee]

You can list of your table names in one table and use that field to alter all tables using one query...

lets say you create a new table with a field called Tables_Names and you list all your tables there..
then

you open a recordset of that table and you use the same SQL to edit all of the tables at one...

 

[The_Doc_Man]

Well, first and foremost, pilgrim, you need to normalize this dataset.

the numbers are for 4 different locatgions, but the table structure is the same.

Well, then, add a field that is your location code and merge the tables by queries one at a time, where for table #1 you supply literal code 1, etc.

Then the queries are all the same except for the location code. And this way, you can even {gasp} do a global operation by not referencing the location code at all.

That also simplifies your problem quite a bit, I would think, because now you don't have to muck about in changing the SQL. Just find a way to supply the location code - which can be a parameter or - if this is driven by a form - you can get the code from the form with a judicious reference to the control where this value is named.

 

[Ziggy1]

Why do you not want to use Four seperate queries?

There are potentially 30 different tables for each location with the same structure. These tables are SQL sources via ODBC from our Main system. It is not a question of normalization

 

[KeithG]

I believe your problem is Normalization. Why not just use the same tables for each site. Maybe add a SiteIndicator Field and another table to enter each sites information. Also I don't see any benifit of using one query and rewritting the SQL each time.

 

[Ziggy1]

Thanks for the replies...

Well, first and foremost, pilgrim, you need to normalize this dataset.



Well, then, add a field that is your location code and merge the tables by queries one at a time, where for table #1 you supply literal code 1, etc.

Then the queries are all the same except for the location code. And this way, you can even {gasp} do a global operation by not referencing the location code at all.

That also simplifies your problem quite a bit, I would think, because now you don't have to muck about in changing the SQL. Just find a way to supply the location code - which can be a parameter or - if this is driven by a form - you can get the code from the form with a judicious reference to the control where this value is named.

I understand the concept, but it is not possible. The source system is designed by location and the ODBC tables are created individually for each location.

currently what I would do is add ALL of the tables and then create a master table and use X number of Append queries which do have a Branch code added to give me a single record set. But rather than create the same query over and over again I just want to create it once and then programically alter the query so it goes to the table source I specify.

I just thought it would keep the database a little more tidy, I mean that is what we try to do when we code a procedure, you put in loops use variables etc..

The other reason that makes it useful is that, when I create the database, I will not always use every locations Tables. But later down the road that location may need to pull up the data, so since the structure is the same, they can just change the Loc code so the query updates from the other location. It just makes it easier for me so I don't have to edit or add queries everytime. Or spend additional time building queries that may not be required.


voskouee seems to have an idea that might work, I'll see if I can apply that idea, but I may still need help with the SQL

 

[Ziggy1]

It could even be further simplified if I could alter the ODBC link to grab from the table i want at it's source, But that could be more complicated?

 

[KeithG]

Okay so your table are in different db's . You need to use the QueryDef object to modify the SQL of your query. If all you are changing is the table name and all of the tables contain the same fields I would just retrieve the SQL from the QueryDef and use the Replace Function to change the table name. Something like below.


Dim qryDef as QueryDef
dim strSQL as string

set qryDef=currentdb.QueryDefs("[QueryNameHere]")

strSQL=qryDef.sql

strSQL=replace(strSQL,[CurrentTableName],[NewTableName])

qryDef.Sql=strSQL
qryDef.close

 

[Ziggy1]

Here is a screen shot of one of my database tables and queries. technically not a Storage database but more for reporting. This is only for 4 locations combining 3 different types of tables.

 

[KeithG]

Paste the below funtion in a module, this should give you a good start.

Public Function UpdateSQL(QueryName As String, CurrentTable As String, NewTable As String)
Dim qryDef As DAO.QueryDef
Dim strSQL As String, strQueryName As String
Dim strCurrent As String, strReplace As String

Set qryDef = CurrentDb.QueryDefs(strQueryName)

strSQL = qryDef.SQL
strSQL = Replace(strSQL, strCurrent, strReplace)
qryDef.SQL = strSQL
qryDef.Close
End Function

 

[The_Doc_Man]

The heartache that comes to mind when I hear the phrase, "this is not possible because..."

OK, assuming you don't want to rock the boat then you have no choices left. In other words, you are already painted into the corner. Down the road this situation will gall you until the bitterness becomes overwhelming.

Having made the editoral comment, here is a thought. Look into UNION queries as a way to build your local copy. You can build one UNION query to tap every separate database. One of the requirements of a UNION query is that you must be able to map the fields into a common format. Well, you have already said the tables differ only in where they are stored, so they ARE in a common format before you even start. With a UNION query you can do

SELECT A, B, C, D, ...., 1 AS LocCode FROM {table at location 1} ... UNION
SELECT A, B, C, D, ....., 2 AS LocCode FROM {table at location 2} ... UNION
etc.etc.

Then use the union table to build the local copy. Then you get to where I said you needed to go. This at least normalizes the local table from which you said you would work.

 

[Ziggy1]

Doc_Man, I never used a UNION Query before, I tried it out but it errors on the ODBC, although it works if I import the tables (not an option due to size )

SELECT lnktblProductInfo_K2.m_prod_001, lnktblProductInfo_K2.m_prod_002, "K2" AS Loc
FROM lnktblProductInfo_K2;


UNION SELECT lnktblProductInfo_C3.m_prod_001, lnktblProductInfo_C3.m_prod_002, "C3" AS Loc
FROM lnktblProductInfo_C3;

this will be of use to me in other areas, but for this request all it does is add all the record sets together, and even though I did describe it that way, I don't wan't all locations updated into a single table at one time a few maybe, but mainly I want to be able to specify which table the query should look at.

I know it will be possible, I'm probably sending you guys in different directions as I try to explain it.


Keith, can you explain the variables further, maybe using my table/querynames in the example.


thanks

 

[KeithG]

Now that I have your SQL from above I have modified the function. Also I have not tested it so let me know if it works.

QueryName=Name of your Query
NewTable=Name of new table for SQL statement

Public Function UpdateSQL(QueryName As String, NewTable As String)
Dim qryDef As DAO.QueryDef
Dim strSQL As String, strQueryName As String
Dim strCurrent As String, strReplace As String
Dim strSelect As String, strFrom As String
Dim strNewT As String

strNewT = NewTable
strQueryName = QueryName

strSelect = "SELECT m_prod_001, m_prod_002,'" & Right(strNewT, 2) & "' AS Loc"
strFrom = " From " & strNewT

Set qryDef = CurrentDb.QueryDefs(strQueryName)


qryDef.SQL = strSelect & strFrom
qryDef.Close
End Function

 

[Ziggy1]

I'm not sure how to trigger the function I created a query based on table lnktblProductInfo_K2, I assume this code would rewrite any query I put in. I used a macro to trigger the function but it won't work.... if you don't mind just explain what I need to do to run it, I know VBA just enough to get by ussually, but there are gaps

Public Function UpdateSQL(QueryName As String, NewTable As String)

QueryName = qryTest
NewTable = lnktblProductInfo_C3

'QueryName=Name of your Query
'NewTable=Name of new table for SQL statement

Dim qryDef As DAO.QueryDef
Dim strSQL As String, strQueryName As String
Dim strCurrent As String, strReplace As String
Dim strSelect As String, strFrom As String
Dim strNewT As String

strNewT = NewTable
strQueryName = QueryName

strSelect = "SELECT m_prod_001, m_prod_002,'" & Right(strNewT, 2) & "' AS Loc"
strFrom = " From " & strNewT



Set qryDef = CurrentDb.QueryDefs(strQueryName)


qryDef.SQL = strSelect & strFrom
qryDef.Close
End Function

 

[KeithG]

You can call the function from a procedure or use the RunCode command of a macro.

In VBA sub you would use

call UpdateSQL("YourQueryName","YourNewTableName")

In a Macro you would use the RunCode command and put

UpdateSQL("YourQueryName","YourNewTableName")



This will just change the SQL not actually run the query. You could add code to run the query also.

 

[Ziggy1]

You can call the function from a procedure or use the RunCode command of a macro.

In VBA sub you would use

call UpdateSQL("YourQueryName","YourNewTableName")

In a Macro you would use the RunCode command and put

UpdateSQL("YourQueryName","YourNewTableName")



This will just change the SQL not actually run the query. You could add code to run the query also.

Ok this is working now, it looks like this is exactly what I am looking for. I just need tie it together. Thank you very much for your help...you too Doc!

 

[Ziggy1]

Hi Keith/Doc, if you still got your "Ears" on this thread maybe you could take a look.


SQL from query design.... the C3 value is a location code that I add to the field, the double quotes denote Text datatype...

LotKey, "C3" AS Branch, Now() AS Stamp
FROM lnktblLotInfo_C3;

this is the code in my VBA (full text below)

LotKey," & "C3 " & "AS Branch, Now() AS Stamp"
strFROM = " FROM " & strNewT & ";"

this is the output from the code, notice the C3 has no quotes, the code does seem to work, but should I not be able to code it so the quotes are included? I tried several different ways, but it was not valid sytax.

LotKey,C3 AS Branch, Now() AS StampFROM lnktblLotInfo_C3;



Full text...

Public Sub Lotinfo()

Dim QueryName As String
Dim NewTable As String


QueryName = "qry_Lotinfo"
' combo to choose table
NewTable = Forms!form1!Combo5

Dim qryDef As DAO.QueryDef
Dim strSQL As String, strQueryName As String
Dim strCurrent As String, strReplace As String
Dim strSELECT As String, strFROM As String, strINSERT As String
Dim strNewT As String

strNewT = NewTable
strQueryName = QueryName

strINSERT = "INSERT INTO tbl_LotInfo_Appended ( Client, Prod, Shrt_BK, Cmpnt_val_1, Cmpnt_val_2, Cmpnt_val_3, Cmpnt_val_4, Cmpnt_val_5, Cmpnt_val_6, Cmpnt_val_7, Cmpnt_val_8, Cmpnt_val_9, Cmpnt_val_10, Cmpnt_val_11, Cmpnt_val_12, Cmpnt_val_13, LotKey, Branch, Stamp )"
strSELECT = "SELECT " & strNewT & ".lots_mst02 AS Client, " & strNewT & ".lots_mst03 AS Prod, " & strNewT & ".lots_mst11 AS Shrt_BK, " & strNewT & ".lots_mst22 AS Cmpnt_val_1, " & strNewT & ".lots_mst24 AS Cmpnt_val_2, " & strNewT & ".lots_mst26 AS Cmpnt_val_3, " & strNewT & ".lots_mst28 AS Cmpnt_val_4, " & strNewT & ".lots_mst30 AS Cmpnt_val_5, " & strNewT & ".lots_mst32 AS Cmpnt_val_6, " & strNewT & ".lots_mst34 AS Cmpnt_val_7, " & strNewT & ".lots_mst36 AS Cmpnt_val_8, " & strNewT & ".lots_mst38 AS Cmpnt_val_9, " & strNewT & ".lots_mst40 AS Cmpnt_val_10, " & strNewT & ".lots_mst42 AS Cmpnt_val_11, " & strNewT & ".lots_mst44 AS Cmpnt_val_12, " & strNewT & ".lots_mst46 AS Cmpnt_val_13, funConvertMavesKey([" & strNewT & "]![lots_mst11]) AS LotKey," & "C3 " & "AS Branch, Now() AS Stamp"
strFROM = " FROM " & strNewT & ";"


' displays SQL string on form for debugging
[Forms]![form1]![Text3] = strINSERT & strSELECT & strFROM

Set qryDef = CurrentDb.QueryDefs(strQueryName)


qryDef.SQL = strINSERT & strSELECT & strFROM
qryDef.Close

DoCmd.OpenQuery QueryName, acNormal, acEdit


End Sub

 

[KeithG]

To put a quote inside a quote use 'string' instead of "string"

 

[Ziggy1]

you mean like

LotKey," & "'C3' " & "AS Branch, Now() AS Stamp"

I just didn't want to run into problems later on, but I guess it works OK like this