Error 3134? 3

[kpryan]

Hi all,

I seem to get this error 3134 when the command button is pressed. Its only a part of some code written by Allan Brown.
I can get all the other code to work but this part.

strSql = "INSERT INTO table1(invoicenumber,acs) " & _
"IN ""C:\integritydb\integritydb.mdb "" " & _
"SELECT invoicenumber, acs, FROM tblinvoicesarch WHERE (acs=True);"
db.Execute strSql, dbFailOnError

I have checked the tables etc, they are all correct, but just don't know why.


Any help would be appreciated.

Ken

 

[TheAceMan1]

Howdy kpryan . . .

Try this:

[blue]   strSql = "INSERT INTO table1 (invoicenumber,acs) " & _
            "IN 'C:\integritydb\integritydb.mdb' " & _
            "SELECT invoicenumber, acs, " & _
            "FROM tblinvoicesarch " & _
            "WHERE (acs=True);"
   db.Execute strSql, dbFailOnError[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

There's also an extra comma before the FROM that needs to be removed

   strSql = "INSERT INTO table1 (invoicenumber,acs) " & _
            "IN 'C:\integritydb\integritydb.mdb' " & _
            "SELECT invoicenumber, acs " & _
            "FROM tblinvoicesarch " & _
            "WHERE (acs=True);"

 

[kpryan]

many thanks for your replies. Works just fine.

Just one more question..
I have about 50 fields I need to archive. Do I have to list all the fields or is there a another way to do this

Many thanks,

Ken

 

[skyline666]

Are the 50 or so fields all the fields in the table? If so you can just do SELECT * (which is select all). If not, and there are only a couple fields you dnt want to select say, then im not sure if theres a way of not selecting a field from all (for example 'select * but not field48') if you see what I mean.

What you could do is if the archiving is to be used in multiple places, then write out the code in a global function, and then call it when you need to. This would save typing out the 50 fields on more then one ocassion.

Andrew

 

[HarleyQuinn]

As Andrew says if you want all of the fields then you could use SELECT *.

I'd personally always list the field names (even if I wanted them all), as this aids with the readability and (much more importantly) maintainability of your code.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.