Access truncates long string

[lynns]

I have a long string similar to the following

Dim sSql2 as String

sSql2 = "INSERT INTO MyTable ( Var1, ..., var13)SELECT " & T1 & " as Var1, ... & T13 & " as Var13;"

DoCmd.RunSQL sSql2

The resulting value of sSql2 is approximately 400 characters.

Access truncates the string to approximately 254 characters resulting into an error with the insert into statement.

I have tried

Dim sSql2 as string * 512

with the same results.

I would appreicate any help.

Thanks

lynns

 

[KenReay]

Hi

Not exactly sure of my facts here, but I think that an SQL string in code is limited to 256 chars.

Are you sure that Access is actuall truncating sSQL2?, have you tried displaying it in the immediate window (eg debug.print sSQL2)?

If this is your problem, then you can either use DAO to insert the record and populate the column ie

Dim Db as Database
Dim RS as Recordset

Set Db = CurrentDb()
Set Rs = Db.OPenRecordset("SELECT * FROM MyTable;&quot
Rs.AddNew
Rs!Var1 = whatever
..etc
Rs.Update
Rs.Close
Set Rs = Nothing
Set Db = Nothing

If you do not want to do it that way, you could create a querydef, populate the SQL property and execute the querydef

Hope this helps
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK