Copying data from one database to another

[Murray]

I have inherted a couple of databases that need some changes.
In the main Database I have a form, if the user clicks on an option in the
option group this should open another database and insert a new record in a
table. The user does not have to see the table in the second database but
needs to get an ID value back
The main database is a job/tender database, the second is a System
Improvement request kept in the OH&S database.
Is this possible?
If yes How is it done.
Thanks in advance

 

[Michael]

Algorithms...
1. User clicks on option
2. Run code to create new record in linked table/different database
3. Function in step 2 to return ID of new record
4. New ID is now known and can be used further

Am happy to provide more, need a bit of feedback...

Cheers
Michael

 

[Murray]

Thanks for the reply, Item 1 is OK have been expermenting with the following:

Case 8 'Warranty opens and creates an SIR
DoCmd.TransferDatabase acLink, "Microsoft Access",
"C:\databases\OHS.mdb", acTable, "SIR", "Rutherford Power"
DoCmd.OpenTable "SIR", acViewNormal, acAdd
The table opens but I can not edit it. How do I get to the last record and
add some information?

 

[Michael]

A couple of questions and a sample code to create the new record...

Why not link the table permanently to the database that is running the
code/option?
The second DoCmd provides little choice/options as to what you want to
enter, below is a function that may provide a better choice...(note: no error
handlers were included although I usually always incl them)

Private Function proc_CreateNewRec () as Long

Dim dbs As Database, rst As Recordset, wrk As Workspace, lngNewID as long

' set return value
proc_CreateNewRec = -1

Set wrk = DBEngine.Workspaces(0)
' next line requires that table is linked into currentDB (ie the one running
the code)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_SIR", dbOpenDynaset)

wrk.BeginTrans
rst.AddNew

rst!SIRCode = 101
rst!SIRNotes = "New SIR record"
rst!SIRDescr = "OHS Record"
rst!SIRDateEntered = Date
' capture new record ID
lngNewID = rst!SIRRecordID

rst.Update
wrk.CommitTrans

' return new record ID
proc_CreateNewRec = lngNewID

rst.Close
qdf.Close
dbs.Close
Set wrk = Nothing

End Function

Hope the above helps...

Cheers
Michael