Solved Select Into Query with varible issue (1 Viewer)

[silentwolf]

Hi guys,

unfortunatelly I can not figure out what I am missing or doing wrong with following SQL String and it feels like I tried all but am not able to get it run without errors.

Public Sub CreateFromExistingTable(selectTable As String, intoTable As String)
    Dim strSQL As String
          
    strSQL = "SELECT  " & selectTable & ".* INTO " & intoTable & "FROM " & selectTable & """"
    
    CurrentDb.Execute strSQL, dbFailOnError
                
End Sub

I like to Create a Table from an Existing Table ..

Can someone please check my SQL String?

Many thanks

Albert

 

[Gasman]

Have you thought to Debug.Print the sql string?

A make table query comes out as

SELECT * INTO Test
FROM Table1;

 

[silentwolf]

HI Gasman,

yes I did debug.print
and if I run SELECT * Into Test
FROM Table1;

it works..
but I would like to have Test and Table1 as varibles so I can use the same query for different tables .(

 

[ebs17]

strSQL = "SELECT S.* INTO " & intoTable & " FROM " & selectTable & " AS S"

Using a table alias makes things clearer because the name of the table can only be used in one place.
You were missing a space before FROM.
With so many DoubleQuotes at the end I'm undecided; I prefer simple spellings.

 

[Gasman]

You can still do that using the syntax as above.

 

[silentwolf]

Thanks You Eberhard!!

Thats the way



Cheers!!

 

[Gasman]

Thanks You Eberhard!!

Thats the way



Cheers!!

Do you even need the alias?

 

[Gasman]

You were missing a space before FROM.

Which a debug.print would have shown.

 

[silentwolf]

Hi Gasman,

hmm not sure if I am honest.. I tried with debuging but was not able to spot it.. Sorry

 

[ebs17]

Do you even need the alias?

Of course not with one table in the source query. However, the table alias is more efficient than using the table name multiple times. If more than one table is used, the question no longer arises.

 

[Gasman]

Hi Gasman,

hmm not sure if I am honest.. I tried with debuging but was not able to spot it.. Sorry

Well the tablename would have been concatenated with the From?

 

[silentwolf]

Well the tablename would have been concatenated with the From?

Hi Gasman,
sorry for late responds.. was not on

No there was no concatination...

 

[Gasman]

Hi Gasman,
sorry for late responds.. was not on

No there was no concatination...

Really?

strSQL = "SELECT  " & selectTable & ".* INTO " & intoTable & "FROM " & selectTable & """"

would show as below with tablenames called as their literal variable names, if you debug.printed strSQL

SELECT  selectTable.* INTO intoTableFROM selectTable

and that should show that From error, regardless of whether the actual syntax is correct or not.

 

[ebs17]

As an addition: To be on the safe side, you could mask the table names with brackets if there could be spaces or special characters.

 

[silentwolf]

In my database are no spaces in table names but I am aware of that thanks for pointing it out Eberhard.

@Gasman..
it was all performed via vba and no concatinations in a form..

And yes there was also missing a space lol but I tried so many different things got stuck ..

Anyway it all works now thanks to all of you guys helping out!

Cheers

 

[silentwolf]

Btw is there now a button somehwere to close the treat or does it stay open?

 

[Gasman]

Btw is there now a button somehwere to close the treat or does it stay open?

Top right, Thread Tools.

 

[cheekybuddha]

it was all performed via vba and no concatinations in a form..

No one mentioned a form!

Time for some new glasses! (or Ctrl and + to increase the scale of your browser)

 

[silentwolf]

Yes I guess so David lol

Thanks Gasman !!