SQL Commands in VBA

[chris.williams]

Hello all - I am attempting to insert some SQL commands which I have tested on the server into VBA code, but I am getting stuck on the temp table creation.

I am attempting to duplicate a large record into a table with a PK and another unique field. My approach is to create a temp table, remove/change the data that needs to be unique and then insert it back into the original table.

I am getting stuck on the first line when it's embedded in VBA where I get a run-time error 2467 - object closed...

So the real question is how do I create a temporary table to do what I want to do and then on the back end, how do I remove it? Thanks...

DoCmd.RunSQL ("CREATE TEMPORARY TABLE drc.tmp AS SELECT * FROM tbl_FTSO WHERE TaskSR='" & Me.SourceSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET id = NULL WHERE TaskSR='" & Me.SourceSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET TaskSR='" & Me.TargetSR & " WHERE TaskSR=" & Me.SourceSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET PHIHIPPA = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET SOX = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET PCI = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET CertificateSummary = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET DateCertificateIssued = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET Environment = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET Facility = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET CEDate = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET RIDCert = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET CertNumber = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET CertType = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET PreceedingCertNumber = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("UPDATE tmp SET RecertificationSR = NULL WHERE TaskSR=" & Me.TargetSR & "';")
DoCmd.RunSQL ("INSERT INTO tbl_FTSO SELECT * FROM tmp;")
DoCmd.RunSQL ("DROP TABLE tmp;")

[ssanfu]

My guess that you are trying to set an autonumber (Identity) field to Null.
Try commenting out this line: (and any other identity field)

DoCmd.RunSQL ("UPDATE tmp SET id = NULL WHERE TaskSR='" & Me.SourceSR & "';")

PS is this one record or multiple records?

[chris.williams]

The issue is that the temp db is not being created. It stops on each line in the code with the same error.

[ssanfu]

Allen Browne has some examples of using DDL code to create a table. (plus other DDL code)
See : http://allenbrowne.com/func-DDL.html#CreateTableDDL

Not knowing how many fields are in a "large record", I would open a filtered recordset and create the insert string on-the-fly, if you are (adding) copying one record. Maybe a little more code, but better than creating and dropping a tmp table every time you want to dup a record.

Another way would be to create the tmp table - don't drop it. Every time you want to copy a record, delete all records in the tmp table, insert the record(s) you want to copy, change the field data, then append the record(s)to the original table. simpler if you don't have to create & delete a table.

My$0.02

[John_G]

Hi -

In your Create temporary table statement - what is drc? Could you not create tmp in the current database? If it is supposed to be in the current database, then the problem is drc.tmp - MS Access doesn't like periods in table names.

John