By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Dynamic SQL with VBA help needed

P: 39
Hello All, I've been searching for over a week and have found a good start but can't seem to make what I want work. I either get an error or a blank table.

What I'm looking for. I have to make a new table called "working" based on another table called "source". I have to do this about once a week but my "source" table changes depending on the week. So I created a "Ref_data" where field1 is the table name of my "source" and field2 are the column names from my "source".

Here is an example of saved query that I'm starting from.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Source].* INTO WORKING " & _
  2.         '       "FROM [Source];
The example of the Ref_data table that pulls my table name and fields.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Ref_data.Field_names FROM Ref_data WHERE (((Ref_data.Field_names) Is Not Null));
Last query that makes my table.
Expand|Select|Wrap|Line Numbers
  1. SELECT ['&strArray&'] INTO WORKING " & _
  2.            "FROM (SELECT [Ref_Data].Table_name FROM Ref_Data);
I've tried i think about a dozen or so ways in VBA, I think this is working but I'm not sure as my SQL select prompts me to enter something before making the table.

I've tried using an array, tried using just the recordset with no luck. Any idea's what I can search for in google or know what my code should be doing to make this work. Below is the code that I have currently. I deleted and started over, again! LOL!

Thanks in advance.
Will
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnStep3_Click()
  2.  
  3.     ' To search for help in RunSQL Method and RunSQL Action in VB
  4.     ' Makes the Working Table from the source data
  5.  
  6.  
  7.    Dim SQL3 As String
  8.    Dim strSQL3 As String
  9.    Dim strArray() As Variant
  10.    Dim rs As ADODB.Recordset
  11.  
  12.    Set rs = New ADODB.Recordset
  13.    strSQL3 = "SELECT DISTINCT Ref_data.Field_names FROM Ref_data WHERE (((Ref_data.Field_names) Is Not Null)); "
  14.    rs.Open strSQL3, CurrentProject.Connection
  15.  
  16.    strArray = rs.GetRows
  17.  
  18.  
  19.     SQL3 = "SELECT ['&strArray&'] INTO WORKING " & _
  20.            "FROM (SELECT [Ref_Data].Table_name FROM Ref_Data); "
  21.  
  22.     DoCmd.RunSQL SQL3
  23.    Set rs = Nothing
  24.  
  25. End Sub
Another attempt that failed is this one.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnStep3_Click()
  2.  
  3.     ' To search for help in RunSQL Method and RunSQL Action in VB
  4.     ' Makes the Working Table from the source data
  5.  
  6.    Dim SQL3 As String
  7.    Dim varArray As Variant
  8.    Dim strArray As Variant
  9.    Dim I As Integer
  10.    Dim cn As ADODB.Connection
  11.    Dim rs As ADODB.Recordset
  12.  
  13.             'Use the ADO connection that Access uses
  14.    Set cn = CurrentProject.AccessConnection
  15.  
  16.    'Create an instance of the ADO Recordset class, and set its properties
  17.    Set rs = New ADODB.Recordset
  18.    With rs
  19.       Set .ActiveConnection = cn
  20.       .Source = "SELECT DISTINCT Refrence_data.Zone_field_names FROM Refrence_data WHERE (((Refrence_data.Zone_field_names) Is Not Null)); "
  21.       .LockType = adLockOptimistic
  22.       .CursorType = adOpenDynamic
  23.       .Open
  24.    End With
  25.  
  26.  
  27.  ' process the recordset
  28.     With rs
  29.         If Not .EOF Then
  30.             .MoveLast
  31.             .MoveFirst
  32.         End If
  33.  
  34.         strArray = rs.GetRows()
  35.        .Close
  36.     End With
  37.  
  38.         If Right(strArray, 1) = "," Then
  39.             strArray = Left(strArray, Len("'&strArray&'") - 1)
  40.         End If
  41.  
  42. SQL3 = "SELECT ['&strArray&'] INTO WORKING " & _
  43.            "FROM (SELECT [Ref_Data].Table_name FROM Ref_Data); "
  44.  
  45.     DoCmd.RunSQL SQL3
  46.    Set rs = Nothing
  47.  
  48. End Sub
Feb 13 '09 #1
Share this Question
Share on Google+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,419
In your first block of code, what do you get if you place :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print SQL3
after line #20?

I find string concatenators (&) tend to fail when not separated by spaces each side.

PS. It seems to me that you need to access an individual element of your array each time. Within a loop. You seem to be trying to pass the whole array into the SQL in one go. Not good.
Feb 13 '09 #2

P: 39
its printing the SQL3 line exactly as it is in line 19.

Expand|Select|Wrap|Line Numbers
  1. SELECT ['&strArray&'] INTO WORKING FROM (SELECT [Ref_Data].Table_name FROM Ref_Data); 
  2.  
  3.  
Feb 13 '09 #3

P: 39
your statement of

"PS. It seems to me that you need to access an individual element of your array each time. Within a loop. You seem to be trying to pass the whole array into the SQL in one go. Not good. "

Yeah.. I attempted this (or at least I thought I attempted this) in the second attempt of code on my orginal post.

I was thinking that I should add the the recordset to an array but everything I was finding online said the recordset is pretty much an array. Another reason I'm lost. I dont think i understand everything about a recordset just yet. Which isn't helping.
Feb 13 '09 #4

Expert Mod 2.5K+
P: 2,545
Following up on NeoPa's lead on line 19, you are missing some double quotes, which go in place of your single quotes. Your debug print is a give-away as the value of the strArray variable has not been substituted in:

Expand|Select|Wrap|Line Numbers
  1. SQL3 = "SELECT ['&strArray&'] INTO WORKING " & _
should be
Expand|Select|Wrap|Line Numbers
  1. SQL3 = "SELECT [" & strArray & "] INTO WORKING " & _ 
-Stewart
Feb 13 '09 #5

P: 39
the double quotes give me an error message, tried few different ways.

Expand|Select|Wrap|Line Numbers
  1. [ " & strArray & "]
  2.  
and
Expand|Select|Wrap|Line Numbers
  1. [ ' " & strArray & " ' ]
  2.  
for both I get an error when i debug with the error of "Compile error: Type Mismatch"
Feb 13 '09 #6

P: 39
In an effort to keep trying I'm posting things that I've tried with no luck so far.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnStep3_Click()
  2.  
  3.     ' To search for help in RunSQL Method and RunSQL Action in VB
  4.     ' Makes the Zone_Working Table from the Zone source data
  5.  
  6.     'SQL3 = "SELECT [43-Account-Zones].* INTO ZONE_WORKING " & _
  7.         '       "FROM [43-Account-Zones]; "
  8.  
  9.    Dim SQL3 As String
  10.    Dim strSQL3 As String
  11.    Dim strArray() As Variant
  12.    Dim rs As ADODB.Recordset
  13.  
  14.    Set rs = New ADODB.Recordset
  15.    strSQL3 = "SELECT DISTINCT Refrence_data.Zone_field_names FROM Refrence_data WHERE (((Refrence_data.Zone_field_names) Is Not Null)); "
  16.    rs.Open strSQL3, CurrentProject.Connection, , adLockBatchOptimistic
  17.  
  18.    ' process the record set
  19.         With rs
  20.             If Not .EOF Then
  21.                 .MoveFirst
  22.                 .AddNew
  23.             End If    
  24.         strArray = rs.GetRows()
  25.         .Close
  26.         End With
  27.  
  28.     SQL3 = "SELECT [' & strArray & '] INTO ZONE_WORKING1 " & _
  29.            "FROM (SELECT [Refrence_Data].Zone_table_name FROM Refrence_Data); "
  30.  
  31.     DoCmd.RunSQL SQL3
  32.    Set rs = Nothing
  33.  
  34.  
  35. End Sub
  36.  
  37.  
and got the error of "Run-time error '-2147217911 (80040e09)': Cannot update. Database or object is read-only."

Which I added the lock that is above and still go this error when the lock record is there.
Feb 13 '09 #7

P: 39
So tried this one..

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnStep3_Click()
  2.  
  3.     ' To search for help in RunSQL Method and RunSQL Action in VB
  4.     ' Makes the Zone_Working Table from the Zone source data
  5.  
  6.     'SQL3 = "SELECT [43-Account-Zones].* INTO ZONE_WORKING " & _
  7.         '       "FROM [43-Account-Zones]; "
  8.  
  9.    Dim SQL3 As String
  10.    Dim strSQL3 As String
  11.    Dim strArray() As Variant
  12.    Dim rs As ADODB.Recordset
  13.  
  14.    Set rs = New ADODB.Recordset
  15.    strSQL3 = "SELECT DISTINCT Refrence_data.Zone_field_names FROM Refrence_data WHERE (((Refrence_data.Zone_field_names) Is Not Null)); "
  16.    rs.Open strSQL3, CurrentProject.Connection, , adLockBatchOptimistic
  17.  
  18.    ' process the record set
  19.         With rs
  20.             If Not .EOF Then
  21.                 .MoveLast
  22.                 .MoveFirst
  23.             End If
  24.  
  25.         strArray = rs.GetRows()
  26.         .Close
  27.         End With
  28.  
  29.     SQL3 = "SELECT [' & strArray & '] INTO ZONE_WORKING1 " & _
  30.            "FROM (SELECT [Refrence_Data].Zone_table_name FROM Refrence_Data); "
  31.  
  32.     DoCmd.RunSQL SQL3
  33.    Set rs = Nothing
  34.  
  35.  
  36. End Sub
  37.  
  38.  
No errors which is good I guess, but my table that is created has 1 field named ' & strArray & ' and 49 records. My source table that its suppose to be grabbing has 14 fields and 65,222 records.

Any idea's?
Feb 13 '09 #8

Expert Mod 2.5K+
P: 2,545
I'm not surprised you get a compile error when you are setting the variable strArray to the result of a getrows operation on the recordset. The name of the variable suggests it is supposed to be a string, except it isn't defined like that (it's a variant).

The SQL Select Into statement operates on a named table or query, and strArray should be a string type whose value is the name of a table or query which you want to write to another table. In the examples you quote you do not need the single quotes, and there should not be spaces between the opening and closing braces and the double quotes.

I get the feeling you have not defined clearly what your algorithm is (i.e. the step-by-step sequence of operations by which you achieve what you have set out to achieve) - and until you do you will keep floundering.

-Stewart
Feb 13 '09 #9

NeoPa
Expert Mod 15k+
P: 31,419
Will, I appreciate you're trying to be helpful here (good attitude of going off and trying things), but when two people are trying to resolve an issue at a distance (as we are - ok not two people, but you and us), then it's actually harder to work with you if you keep moving on without us.

We like to focus on an issue until it's resolved. We drill down and find out more and more until we suss the issue. Moving onwards makes that very difficult.

So, with your indulgence let me take you back to the issue we were discussing earlier. Stewart was right on topic when he indicated where the problem lay. It's exactly why I asked the question.

The problem is, we need to look at how you need to be processing through multiple items from your [ref_data] table. That wasn't explained too clearly, and I'm a little hazy on that. These details are so critical because without this info, everything else is built on sand. In database work it is essential to pay attention to the details. Design is built upon, and if it is faulty... well, you must know what happens then, and it's not pretty. We make our lives easier, by paying attention to detail from the start. By the way, that was also why I completely ignored your second piece of code. It is simply a diversion. You may get lucky that way, but until you understand what you're doing and why, you will forever be reliant on high levels of luck.

I hope that didn't sound too much like a lecture. I simply wanted you to understand why we need to work the way we do.

Let's start by trying to understand better what you're actually trying to do. So, what type of output would you have expected to see in SQL3 when it was displayed? Presumably one of the various fieldnames stored in the records of Ref_data.Field_names?
Feb 13 '09 #10

NeoPa
Expert Mod 15k+
P: 31,419
Sorry Stewart, was working on this one a fair time. Before your post.

Having said that - it seems we're not very far apart on where we're coming from unless I misread you.
Feb 13 '09 #11

P: 39
I'm guessing your 100% accurate. I'm stuck as I know what I want it to do, but I don't think I know the steps it has to do in order for it to work like this.

What I'm wanting is....

Source_table has 14 fields named 1-14

I'm adding a ref_data table with the first field as table_name and it has 1 record of "source_table" and the second field is table_fields and has 14 records that are the field names from the "source_table"

I need a dynamic query as the source_table name will change as will the field names.

So Query 1, I want to

Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT [Ref_data].Field_names FROM [Ref_data].Table_name WHERE [Ref_data] Is Not Null;"
  2.  
I would like this information placed in my strArray variable (not sure Im even close to getting this into this variable. Which could be my problem)

then once this information is in my strArray variable to run this query and make my table.

Expand|Select|Wrap|Line Numbers
  1. "SELECT [' & strArray & '] INTO WORKING FROM (SELECT  Ref_Data].Table_name FROM Ref_Data); "
  2.  
I'm wanting this to create a copy of this source table, which in this case is 14 fields and 65,200 something records.

Am I missing a step between the recordset and trying to get the record set into the variable.

I was attempting placing the recordset into an array, but no luck and I'm not sure how I can test that the recordset is getting into the array. Then doing this

Expand|Select|Wrap|Line Numbers
  1.     If Right(strArray, 1) = "," Then
  2.         strArray = Left(strArray, Len("'&strArray&'") - 1)
  3.     End If
  4.  
To format the array into one line with the comma for the proper syntax for a select statement.

Am I going in the right direction? Idea's, thoughts suggestions?

Thanks again!!
Will

PS- I was typing this response i think while both of you were sending your last reply too. Did I answer the last question Stewart?
Feb 13 '09 #12

NeoPa
Expert Mod 15k+
P: 31,419
We may have to get back onto this for you again tomorrow Will. I'm heading off for the night and Stewart (also British) seems to have gone already.
Let's see where we can get to over the weekend.

I must say, although moving from one thing to another makes life a little difficult, I do commend you for your attitude. It takes courage to throw away and start again. I don't think it's your best course of action now, as we can hopefully provide some guidance to take you where you want to go more directly, but it shows a good attitude.

Let's see what the weekend brings.
Feb 13 '09 #13

Expert Mod 2.5K+
P: 2,545
NeoPa and I have been trying to find out what it is you are really trying to achieve. In some ways you have shown the perils of making assumptions about how to achieve it - you have gone off at a tangent about recordset field arrays that are irrelevant to what you are trying to do, which is to take copies of tables within your database, allowing for the names of the tables to be changed as the need arises. You don't need to know the field names of the tables concerned - in SQL you can use SELECT * to select all fields in a table.

The actual code to do this is fairly simple. You need to create a table in which you list the names of the tables you need to copy, along with an optional name for each table's copy. Then you can use the fCopyTables function which calls fCopyTable below to copy the set of tables:

Expand|Select|Wrap|Line Numbers
  1. Public Function fCopyTable(ByVal strTableFromName As String, Optional ByVal strTableToName As String = "") As Boolean
  2.     'Copies table named in strTableFromName to a new table named in strTableToName
  3.     'If strTableToName is not supplied, the date is added to the name
  4.     'of the original table as the copy to name.
  5.     'Returns True if copy succeeded, False if not
  6.     '
  7.     On Error GoTo errhandler
  8.     Dim strSQL As String
  9.     If strTableToName = "" Then
  10.         strTableToName = strTableFromName & "-" & Format(Date, "yyyy-mm-dd")
  11.     End If
  12.     strSQL = "SELECT * INTO [" & strTableToName & "] FROM [" & strTableFromName & "];"
  13.     DoCmd.SetWarnings False
  14.     DoCmd.RunSQL strSQL
  15.     DoCmd.SetWarnings True
  16.     fCopyTable = True
  17.     Exit Function
  18. errhandler:
  19.     MsgBox "Error: " & Err.Number & " - " & Err.Description
  20.     fCopyTable = False
  21. End Function
  22.  
  23. Public Function fCopySetofTables(strRecordSetName As String)
  24.     ' Loops through a recordset (query or table) based on the tblCopyTables
  25.     ' table in which the names of tables to copy are listed.
  26.     ' Requires that the database has a reference to the DAO project library
  27.     ' - from VB editor select Tools, References and ensure that
  28.     '   Microsoft DAO x.x object library is selected.
  29.     '
  30.     Dim RS As DAO.Recordset
  31.     Set RS = CurrentDb.OpenRecordset(strRecordSetName)
  32.     With RS
  33.     Do Until .EOF
  34.         If Not fCopyTable(![TableFrom], Nz(![TableTo])) Then
  35.             MsgBox "Copy of table " & ![TableFrom] & " failed", vbCritical
  36.         End If
  37.         .MoveNext
  38.     Loop
  39.     End With
  40.     RS.Close
  41. End Function
The function fCopySetofTables can be called from the on-click event of a command button on a form somewhere, with the name of the table containing the copy list supplied as an argument.

The name of the table in which the tablenames are stored is not critical. In my example it is called tblCopyTables, and contains two text fields: TableFrom and TableTo. TableTo is optional - a default is assigned if no To name is provided.

The reason for supplying the table name as an argument to function fCopySetofTables is that the copy can be generalised further by supplying the name of a suitable query in place of the table. A query could select specific tables for particular purposes, so that you do a selective copy (e.g. by filtering on additional fields added to the tblCopyTables for that purpose).

I attach a simple example database as illustration of the use of the copy routines, including a selective copy query based on filtering an additional criterion in another list table.

-Stewart
Attached Files
File Type: zip Copy Tables.zip (22.0 KB, 57 views)
Feb 15 '09 #14

P: 39
Excellent!! I was using the array as I hadn't heard of the fCopyTable option. I think this is exactly what I was looking for and didn't know what it was called. Happens on an everyday basis! LOL!

Thanks so much Stewart for the example and help!! And thanks to NeoPa for the help as well! I wont get to try it till tomorrow at work. I forgot to send a copy of my database home so I could work on it today. Opps! Guess I'll have to enjoy the rest of Sunday!

Thanks again!
-Will
Feb 15 '09 #15

Expert Mod 2.5K+
P: 2,545
No problem Will. Please note that fCopyTable is a custom function based on another one I wrote for a slightly different purpose - it does not exist as a supplied function within Access. It is contained in the public code module in the example database.

I routinely copy ODBC-linked tables from one database to unlinked physical copies in another Access database located on a different server, so I simply adapted the copy function concerned to a new use.

-Stewart
Feb 15 '09 #16

NeoPa
Expert Mod 15k+
P: 31,419
Thanks for getting this Stewart.

I'd intended to be more available this weekend, but I ended up spending most of my time trying to get code-signing to work in VBA.

I'll be putting something together shortly to say what I managed to get working (and how).

I see you have this pretty well covered anyway. Nice.
Feb 15 '09 #17

Post your reply

Sign in to post your reply or Sign up for a free account.