Solved Error While Running Sql Include Table Name As Variable

[ahmedjamalaboelez]

Good day Brothers,
I tried to replace tables names with a variable , so i can insert specific table , is that right to include table name as variable in method !!
Before i include Table1 and Table2 As Variables it was working fine ,
any solution ? Code Below

Code Is Here :

Public Sub PendSpecTable(TableName1 As String, Tablename2 As String, IndexID As String)
    Dim sql1 As String
    Dim sql2 As String
    sql1 = "INSERT INTO '" & TableName1 & "' SELECT lc_databases.* FROM lc_databases," & _
           "WHERE (((lc_databases.database_id)='" & IndexID & "')),"""
    sql2 = "INSERT INTO '" & Tablename2 & "' SELECT lc_dbconnections.* FROM lc_dbconnections," & _
           "WHERE (((lc_dbconnections.cnn_databaseid)='" & IndexID & "')),"""
    DoCmd.RunSQL sql1
    DoCmd.RunSQL sql2
End Sub
' Define Listview Right To left------------------------------------
Public Sub list_search_Click()
    [frm_idx] = list_search.SelectedItem.text
    [sub_databases_view].SetFocus
    FunDeleteLocal
    'FunPendLocal
    PendSpecTable "lc_databases_view", "lc_dbconnections_view", list_search.SelectedItem.text
    FunSelectMode
    [sub_databases_view].Requery
    [sub_dbconnections_view].Requery
End Sub

I Appreciate your Help;
Thanks
A.J

 

[The_Doc_Man]

Syntactically, the apostrophes around Tablename1 & Tablename2 are probably wrong because if there were no concatenation, that name would not be quoted. The syntax is INSERT INTO tablename SELECT ... (without quotes around the table name).

The apostrophes around IndexID might be right if that is a string, but not if IndexID is numeric.

However, semantically it appears that you are creating an identical copy of subsets of two tables when you could get the same result using a query with the same WHERE clauses to restrict what you are doing. AND it is possible that such a query would be updateable so you would use the query in place of a table.

It's your app so I will assume you had a reason to do it that way, but it IS a somewhat wasteful choice.

 

[ahmedjamalaboelez]

Syntactically, the apostrophes around Tablename1 & Tablename2 are probably wrong because if there were no concatenation, that name would not be quoted. The syntax is INSERT INTO tablename SELECT ... (without quotes around the table name).

The apostrophes around IndexID might be right if that is a string, but not if IndexID is numeric.

However, semantically it appears that you are creating an identical copy of subsets of two tables when you could get the same result using a query with the same WHERE clauses to restrict what you are doing. AND it is possible that such a query would be updateable so you would use the query in place of a table.

It's your app so I will assume you had a reason to do it that way, but it IS a somewhat wasteful choice.

GoodDay Mr. The_Doc_Man
I hope you have a nice Day

My IndexID is string yes.
I did this way because I has many tables in database with different names but same structure data inside for some reasons,
So What's the best way to change table name without repeating code every time ,
Thanks

 

[June7]

Brothers and Sisters?

Use [ ] characters to define objects and they are only necessary if object names include spaces or punctuation/special characters or are reserved words or all numeric (advise to avoid all) - so never hurts to use.

"INSERT INTO [" & TableName1 & "] SELECT

Apostrophes and quotes are text delimiters for literal strings. Use # character for date/time.

Really sounds like database structure is not optimized.

 

[ahmedjamalaboelez]

June7​

I tried To Replace but same error

Brothers and Sisters?

Use [ ] characters to define objects and they are only necessary if object names include spaces or punctuation/special characters or are reserved words or all numeric (advise to avoid all) - so never hurts to use.

"INSERT INTO [" & TableName1 & "] SELECT

Apostrophes and quotes are text delimiters for literal strings. Use # character for date/time.

Really sounds like database structure is not optimized.

 

[June7]

Exactly what error message do you get?

Have you step-debugged? Set a breakpoint and step through code to examine content of variables.

Use Debug.Print sql1 so you can examine the constructed SQL statement in Immediate Window. Can even then copy/paste into a query object to see if it would execute there.

 

[ahmedjamalaboelez]

Exactly what error message do you get?

Have you step-debugged? Set a breakpoint and step through code to examine content of variables.

Use Debug.Print sql1 so you can examine the constructed SQL statement in Immediate Window. Can even then copy/paste into a query object to see if it would execute there.

 

[ahmedjamalaboelez]

Exactly what error message do you get?

Have you step-debugged? Set a breakpoint and step through code to examine content of variables.

Use Debug.Print sql1 so you can examine the constructed SQL statement in Immediate Window. Can even then copy/paste into a query object to see if it would execute there.

Public Sub PendSpecTable(TableName1 As String, Tablename2 As String, IndexID As String)
    Dim sql1 As String
    Dim sql2 As String
    sql1 = "INSERT INTO " & TableName1 & " SELECT lc_databases.* FROM lc_databases WHERE (((lc_databases.database_id)='" & IndexID & "'))"
    sql2 = "INSERT INTO " & Tablename2 & " SELECT lc_dbconnections.* FROM lc_dbconnections WHERE (((lc_dbconnections.cnn_databaseid)='" & IndexID & "'))"
           Debug.Print sql1
    DoCmd.RunSQL sql1
    DoCmd.RunSQL sql2
End Sub

I think its running perfect now , The Problem was not in tables Variables was in break lines in Sql code its self
Thanks

 

[arnelgp]

what Datatype is IndexID, numeric?
you remove the single quote in your expression if it is numeric.
also both source and target table must have same field count when you execute your insert query
when you did not specify which field to insert.

 

[June7]

Ooomf!!! Now I see the bad commas in the original code. Glad you found and fixed.

 

[ahmedjamalaboelez]

what Datatype is IndexID, numeric?
you remove the single quote in your expression if it is numeric.
also both source and target table must have same field count when you execute your insert query
when you did not specify which field to insert.

Thanks Mr. arnelgp

 

[ahmedjamalaboelez]

Ooomf!!! Now I see the bad commas in the original code. Glad you found and fixed.

Thanks Mr. June7