SQL Syntax

[Peter Bellamy]

Can someone please advise the way to include the quotes for a path in a SELECT INTO IN FROM statement.
The following does not seem to like the path section?

StrSQL = "SELECT Parts.* INTO [Parts Old] IN 'Z:\Database\2000\Service2k.mbd' FROM Parts;"

Cheers

 

[Kryst51]

Check out this it talks about how to make sql read as quotes, or string, etc. I THINK that you need to separate your SQL with something like:

StrSQL = "SELECT Parts.* INTO [Parts Old] IN" & " Whatever the correct way to do the SQL here. " & " FROM Parts;"

 

[kevlray]

It has been while since I have done it, but I believe you put double quotes where you want one.

StrSQL = "SELECT Parts.* INTO [Parts Old] IN ''Z:\Database\2000\Service2k.mbd'' FROM Parts;"

 

[Peter Bellamy]

Thanks.
I had tried the " & " path " & " solution but it does not leave the path in quotes which I assume it has to have.

 

[Kryst51]

Did you try this:

" ... myField = " & Chr(34) & MyText & Chr(34) & " ... "

solution?

 

[Peter Bellamy]

I haven't but it is the same as alternating quotes.
" & Chr(34) & Text & Chr(34) & " the same as " ' Text ' "

Which I have tried

 

[Kryst51]

Do you get any errors at all? Can you post the code which uses the new sentax?

 

[Peter Bellamy]

Thanks for sticking with this!
The error is :

Could not find file 'Z:\Database\2000\Service2k.mbd'

A debug.print of the SQL before the SQL is actioned is:

SELECT Parts.* INTO [Parts Old] IN 'Z:\Database\2000\Service2k.mbd' FROM Parts;

Which makes me think the single quotes are not allowing the path to be read as a text string.

There is just one other possibility. I am developing this on a PC at home, not at work. I have set up a network drive as z: with the correct folders and file. Perhaps it does not work exactly like it would on a 'proper' network?

I will simplfy the path top the local HDD and see if that works.

Cheers

 

[Peter Bellamy]

Tried it, same error !

 

[vbaInet]

You don't need quotes and the extension is incorrect, it's mdb not mbd. Try one of:

StrSQL = "SELECT Parts.* INTO [Parts Old] IN Z:\Database\2000\Service2k.mdb FROM Parts;"

StrSQL = "SELECT Parts.* INTO [Parts Old] IN Service2k.mdb FROM Parts;"

 

[Peter Bellamy]

Dyslexia rule, KO !!
Correcting that and using no quotes gives an error on the line:

SELECT Parts.* INTO [Parts Old] IN Z:\Database\2000\Service2k.mdb FROM Parts;

of

Query input must contain at least one table or query

 

[Peter Bellamy]

However putting the single quotes back does!

Thanks everyone

 

[Kryst51]

However putting the single quotes back does!

Thanks everyone

I am glad you got it sorted.

 

[vbaInet]

What version of Access are you using? 2000?