SQL path parameter

[Jackalx25]

Hi all,

I am trying to use the follwoing SQL in my VB app which uses the DE;

INSERT INTO TableB ( Field1, Field2) IN dbServer
SELECT Field1, Field2
FROM TableA;

where dbServer is the path parameter for TableB.

I keep getting a syntex error and have tried using a second variable dbServerB variable as:

dbServerB = " ' " & dbServer & " ' "

and using dbServerB in the SQL.

I have set the parameter property to adBSTR.


Any solution would be much appreciated.

Many Thanks

Jack

[JMvVliet]

Isn't it true that params only can be used with fields? I thought so, and that could explain this won't work.

[Ecniv]

dbServerB = " ' " & dbServer & " ' "

too many spaces?


Thought In was in the [i]From part of the Sql??

Vince

[Jackalx25]

The spaces I used in the " ' " were intentionally to make what I was doing a little clearer (guess not!) instead of using "'", sorry for misleading you.

Anyhow, I have come up with what is probably an inelegant way of handling this problem;

With DE
.rscomTableA.Open
.rscomTableB.Open

Do Until .TableA.EOF = True

.rscomTableB.AddNew
.rscomTableB.Fields("A") = .rscomTableA.Fields("A")
.rscomTableB.Fields("B") = .rscomTableA.Fields("B")
.rscomTableA.MoveNext

Loop

.rscomTableA.UpdateBatch
.rscomTableA.Close
.rscomTableB.Close

End With

This append will only ever have a max of about 40 records, so
hopefully will be ok for my purposes.

A more elegant solution would be most welcome.

Cheers

Jack