string concatenation

[sandyarela]

Hi,
I have a question on string concatenation.
Some code background info: In the form frmMainMenu, there is a text box that gets the computer name 'txtUserID'. I'm trying to create unique table names according to individual that runs the code. qryMaker is just a query.

Here's the code:

Dim tblName As String
Dim strSQL As String

tblName = "tblQueryMaker" & [Forms]![frmMainMenu]![txtUserID]
strSQL = "SELECT * INTO " & tblName & " FROM qryMaker;"

DoCmd.RunSQL (strSQL)

I've tried DoCmd.RunSQL and .execute, but both get the same error message of " must have at least one destination field." It seems that " FROM qryMaker;" does not get concatenated to strSQL, and I can't figure out why.

Thanks!

 

[jzwp22]

You could add a debug.print strSQL statement after you construct the query text but before the docmd. The debug.print statement will push the query string to the Immediate Window and you can check the SQL text to see if it is OK. You could also copy the text from the Immediate Window into a new query and attempt to run it to see if errors are produced. BTW, I copied your code into a test database and it ran fine.

BTW, why would you create a table for each user? What happens if you have 100 users? or 10000 users? In either case, that would be a table management nightmare. Having multiple tables with the same structure violates normalization rules. Why not append records into 1 table with a field identifying the user.

 

[VilaRestal]

Agreed with what jzwp22 says. Don't have a table per user. That is bad. There is no justification for such a thing ever.

But for reference, the correct sequel syntax for the insert statement is:

"INSERT INTO " & tblName & " SELECT * FROM qryMaker;"

(and will only work properly if tblName and qryMaker having exactly the same fields in the same order and no autonumber fields in tblName. If that's not the case then you need to add the field destinations and select the fields you want:
"INSERT INTO " & tblName & " (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM qryMaker;"
)

But please stop now and change this awful design where each user has a table. It's a terrible anti-pattern

 

[sandyarela]

This code is part of ad hoc query maker, where the user selects items from a form and the SQL code is then generated. The ad hoc query maker works fine, even with multiple users. The problem came when it was requested that the produced query have the rows numbered, kind of like Excel. I tried using various count operations, but with the tens of thousands records, it was taking way too long or would simply freeze. So for speed purposes, the generated query (qryMaker) is put into a table and then an auto number column is inserted. Since there are multiple users, the same table can't be used for all ad hoc queries. So, each user has their table generated **issue having right now**, which is then automatically deleted by another set of code when they exit the form. I've noticed that when I run the code in a mode with all securities bypassed and a generic name is placed into txtUserID, all this code works perfect. It's only when txtUserID actually gets the computer name, this part of the code bombs.
In following jzwp22's advice with debugging, "SELECT * INTO tblQeryMakerCompName " is only generated.
When using VilaRestal's code, I get "Run-time error '3134 Syntax error in INSERT INTO statement. " . The generated code is "INSERT INTO tblQeryMakerCompName ".
So, it still seems like after getting the computer name, nothing afterwards is being concatenated to strSQL .
If there is a faster way to get the rows automatically numbered on a ad hoc query, I'm open to suggestions/advise

 

[VilaRestal]

I think that error is basically saying it doesn't recognise that table.

And now I see that original code was a make table one. Mine wasn't.

I suspect that make table syntax doesn't work as runsql or execute.

Perhaps you could create a querydef with that sql and open that:

Dim tblName As String
Dim strSQL As String

tblName = "tblQueryMaker" & [Forms]![frmMainMenu]![txtUserID]
strSQL = "SELECT * INTO " & tblName & " FROM qryMaker;"

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Type = dbQMakeTable
qdf.Execute

But it would be better not to create a table if at all possible.

Perhaps show us the SQL behind qryMaker. Maybe it could be improved.

 

[sandyarela]

Here is the overall code. The error occurs in
Private Function MakeTbl(Cancel As Integer).
Sorry, I will not be able to send any other information such as tables, queries, or forms.

 

[jzwp22]

I could not open your attachment. Are you using Visual Basic? When I posted my earlier response, I was using Visual Basic for Application (VBA) from within Access.

 

[VilaRestal]

It is a VBA module. The code for the MakeTbl is:

Private Function MakeTbl(Cancel As Integer)

'this funtion makes the query into a table

'table name includes PSons name
'so there will be no conflict if multiple people execute the code at the exact same time
'note, every individual will have thier own table

Dim tblName As String
tblName = "tblQueryMaker" & [Forms]![frmMainMenu]![txtUserID]



Dim tblExists As Integer

tblExists = fExistTable(tblName)

'if table aleady exists and is open
'then close and delete it
If tblExists <> 0 Then

DoCmd.Close acTable, tblName, acSaveNo
DoCmd.DeleteObject acTable, tblName

End If

'*******************************************
'            ERROR OCCURS HERE
'*******************************************

Dim strSQL As String
strSQL = "INSERT INTO " & tblName & " SELECT * FROM qryMaker;"

'MsgBox strSQL, vbInformation ' for debugging

Debug.Print strSQL



    DoCmd.RunSQL (strSQL)

'adds autonum field for row #

    Set curDatabase = CurrentDb
    Set tblTest = curDatabase.TableDefs(tblName)
                
    strField = "Row#"
    
    Set fldNew = tblTest.CreateField(strField, dbLong)
    With fldNew
    .Attributes = .Attributes Or dbAutoIncrField
    End With
    
    tblTest.Fields.Append fldNew
    DoCmd.OpenTable tblName, , acReadOnly
  End Function

The place you say the error occurs: after an End If and before a Dim?! That makes no sense.

Did you read my previous response? Did you try that code (using the querydef)?

 

[sandyarela]

=> I did try the code and on qdf.Type = dbQMakeTable I get "Compile error: Can't assign to read-only property"

=> In my code, where it says error occurs in order to help identify the location of code snippet from original first post.
But at actual compile time, it gets hung up at DoCmd.RunSQL (strSQL)

 

[VilaRestal]

I see, that was a guess, then comment out the 'qdf.Type = dbQMakeTable

It's another way to execute a SQL. Perhaps you should try it considering we know DoCmd.RunSQL with that SQL isn't working.

(And I've got to ask: why post your code as a zipped .vb file?)