Syntax problem in SQL

[PiedPiper70]

Here is a snippet of code I'm going to use for archiving old data

strSQL = "INSERT INTO tblMatters " & -
"IN ""C:\Archive\DataArchive.mdb"" " & -
"SELECT * FROM tblMatters WHERE [rec_date] < #1 January 2009#;"

The code appends certain records from a local table into an identical table in an external database. The probem I have is that the external db requires a password and I just can't get the syntax right.

[Original code courtesy of Allen Browne]

Any help gratefully received
Thanks
Dave

 

[pr2-eugin]

Dave, I am sorry to say this.. But I do not have the answer to your problem.. Although I have a suggestion.. why not link the ArchiveDB tables to the CurrentDB.. This way you do not have to worry about this whole issue?

 

[CJ_London]

try

SqlStr="INSERT INTO [C:\Archive\DataArchive.mdb;PWD=????].tblMatters 
SELECT * FROM tblMatters WHERE [rec_date] < #1 January 2009#;"

replace the ???? with the password of the archive db, do not surround with quotes

Also I haven't tried it your way but #1 January 2009# would normally be stated as #01/01/2009# if your way doesn't work.

 

[PiedPiper70]

A thousand thanks CJ - worked a treat.

I tend to use the "long" way of writing dates as it saves confusion with the old American month/day/year issue, and Access understands it perfectly