Copy from one DB to another

  • Thread starter idmnstr via AccessMonster.com
  • Start date
I

idmnstr via AccessMonster.com

Hi,
I created the following attached to a button to copy the record my form is
currently displaying to another DB that is exact duplicate, structure only.

I get an error saying that "mdb\NewReport" table can't be found.

Private Sub CopyToImm_Click()

OldSQL = "INSERT INTO " & _
"U:\CIS\DAR\MyName\AccessDB\Jims_Immediate_DB.mdb\NewReport " & _
"SELECT * FROM NewReport " & _
"WHERE NewReport.ReportId = [ReportId];"

DoCmd.RunSQL OldSQL

End Sub
 
K

Klatuu

It is a syntax problem
OldSQL = "INSERT INTO NewReport" & _
"IN U:\CIS\DAR\MyName\AccessDB\Jims_Immediate_DB.mdb " & _
"SELECT * FROM NewReport " & _
"WHERE NewReport.ReportId = " & Me.[ReportId];"

Also, notice the WHERE line. Your reference to an object has to be outside
the quotes. The above syntax assumes ReportId in the NewReport table is a
numeric data type. If it is a text data type, this would be the syntax:
"WHERE NewReport.ReportId = '" & Me.[ReportId]; & "'"
 
J

John W. Vinson

Hi,
I created the following attached to a button to copy the record my form is
currently displaying to another DB that is exact duplicate, structure only.

I get an error saying that "mdb\NewReport" table can't be found.

Private Sub CopyToImm_Click()

OldSQL = "INSERT INTO " & _
"U:\CIS\DAR\MyName\AccessDB\Jims_Immediate_DB.mdb\NewReport " & _
"SELECT * FROM NewReport " & _
"WHERE NewReport.ReportId = [ReportId];"

DoCmd.RunSQL OldSQL

End Sub
Click to expand...

A Database is not a table; and a table is not a file; and a report is not a
table either!

This seems like a VERY convoluted way to do things. What does a Report have to
do with copying a record? Why two .mdb files? Just what are you trying to
accomplish?

You *might* be able to use something like

OldSQL = "INSERT INTO NewReport IN " & _
"""U:\CIS\DAR\MyName\AccessDB\Jims_Immediate_DB.mdb"" " & _
"SELECT * FROM NewReport " & _
"WHERE NewReport.ReportId = [ReportId];"

but it still seems like a complicated approach!

John W. Vinson [MVP]
 
I

idmnstr via AccessMonster.com

John,

It appears that I made your day. I am very happy I was able to supply some
small amount of entertainment.

I would still like to thank you and Klatuu for your suggestions.

I tried the code you posted and it warned me that I was going to add ALL of
the records to the second DB.

Although this is pretty neat, It was not really what I had in mind.

I just wanted to add the one record. The key to the table is the ReportId.

Regards,
Jim
 
J

John W. Vinson

Although this is pretty neat, It was not really what I had in mind.

I just wanted to add the one record. The key to the table is the ReportId.
Click to expand...

Follow Klatuu's suggestion, then - pull the criterion form reference out of
the string.

John W. Vinson [MVP]
 
I

idmnstr via AccessMonster.com

Klatuu,

While I was waiting for an interesting reply, I tried a segment of your
suggestion;
"WHERE NewReport.ReportId = Me.[ReportId];"

A prompt appears requesting a value for Me.[ReportId] If I enter the
ReportId of the sending DB it does in fact add it to the second DB but with
the same key value of the sending DB.

Once again, This is neat and closer to what I am looking for in a solution.

I can see at least one problem with this method. I don't know if that key
value is already in use in the second DB. I was just lucky this time.

Regards,
Jim
 
Top