Transfer to another DB

[bigal.nz]

Hiya People,

I am trying to check if a record exists before an INSERT statement. Easy enough on the same database and I see a few answers already here, but what if you want to chek if the record exists in a seperate database?

I tried something like:

Private Sub btn_transfer_Click()

On Error GoTo Err_Transfer
    Dim ws As Workspace
    Dim db As DAO.Database
    Dim bInTrans As Boolean
    Dim strSQL As String
    Dim strMSG As String
    
    Dim rsc As New ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = ????
    
    ' Initalise DB Object inside a transaction
    Set ws = DBEngine(0)
    ws.BeginTrans
    bInTrans = True
    Set db = ws(0)
    
    ' Check if record exists?
    
    strSQL = "SELECT * FROM SAMNZ IN ""C:\Users\Owner\Desktop\DB\test.accdb"" WHERE (EventNo='" & EventNo & "');"
    Debug.Print (strSQL)
    rsc.Open strSQL
    If rsc.EOF Then
        ' Execute the append
        strSQL = "INSERT INTO SAMNZ IN 'C:\Users\Owner\Desktop\DB\test.accdb' SELECT * FROM table1 WHERE (IDNo='" & IDNo & "');"
        Debug.Print (strSQL)
        db.Execute strSQL, dbFailOnError
    End If
        
Exit_DoTransfer:
    ' Cleaning Up
    On Error Resume Next
    Set db = Nothing
    If bInTrans Then
        ws.Rollback
    End If
    Set ws = Nothing
    rsc.Close
    Set rsc.ActiveConnection = Nothing
Exit Sub

Err_Transfer:
    MsgBox Err.Description, vbExclamation, "Transfer Failed: Error " & Err.Number
    Resume Exit_DoTransfer
End Sub

 

[Galaxiom]

Why not link the table?

 

[bigal.nz]

What if both databases have a table of the same name?

In the SQL how would you distingush one table from the other?

Update: When I linked the tables with the same name, the second instance got a "1" on the end

 

[Galaxiom]

What if both databases have a table of the same name?

In the SQL how would you distingush one table from the other?

The linked table will import with a different name from any that are already in the database. The local name of linked tables can be changed to anything you like.

 

[bigal.nz]

I think I am getting closer but still getting a error:

Error 3709
The connection can not be used to perform this operation. It is either closed or invalid in this context.

On line: rsc.Open strSQL

This is the current code:

Private Sub btn_transfer_Click()

On Error GoTo Err_Transfer
Dim ws As Workspace
Dim db As DAO.Database
Dim bInTrans As Boolean
Dim strSQL As String
Dim strMSG As String
    
' Initalise DB Object inside a transaction
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)
    
' Check if record exists?
Dim rsc As New ADODB.Recordset
Dim conn As ADODB.Connection
Set rs = New ADODB.Recordset
Set conn = CurrentProject.Connection
    
strSQL = "SELECT * FROM SAMNZ_NT WHERE (IDNo='" & IDNo & "');"
Debug.Print (strSQL)
rsc.Open strSQL
If rsc.EOF Then
' Execute the append
strSQL = "INSERT INTO SAMNZ_NT SELECT * FROM SAMNZ WHERE (IDNo='" & IDNo & "');"
Debug.Print (strSQL)
db.Execute strSQL, dbFailOnError
End If
......
......
.....

 

[bigal.nz]

Connection string was messed up.

    rsc.Open strSQL, conn, 0, 1

Works.

 

[Galaxiom]

Why not simply use a DCount instead of all that recordset code?

 

[bigal.nz]

I suppose I can now that I am not trying to open a file.

However the more interesting issue the button transfers a record from one table to another with identical fields.

I think I will run into issues if I try SELECT * (because this will include primary key) and when I try to insert primary key into second table there may be a conflict? Or does access handle this for me?

Ta

Al

 

[bigal.nz]

Ok - to avoid trying to insert the record into the new table with a primary key I am:

1. Checking record doesnt already exist in target table (SAMNT)
2. Getting the field names and values into strings (minus the Primary key ID)
3. How to get the values into a SQL string in a SQL safe syntax (ie not worrying about , or ' in the strings.

Private Sub btn_transfer_Click()

Dim field As ADODB.field
Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM SAMNT WHERE IDNo='" & IDNo & "';", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.EOF Then
    MsgBox ("Record Does Not Exist in target table")
    rs.Close
    rs.Open "SELECT * FROM SAM WHERE IDNo='" & IDNo & "';", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    For Each field In rs.Fields
        ' Debug.Print (field.Name)
        If field.Name <> "ID" Then
        strFields = strFields & field.Name & ","
        strValues = strValues & field.Value & ","
        End If
    Next field
End If

Debug.Print (strFields)
Debug.Print (strValues)

I would like to do it this way as I can change my DB without changing the code.

Cheers

Al

 

[static]

If you do it that way you'd also need to worry about datatypes.

To specify another db use [database].table

If CurrentDb.OpenRecordset("select 1 from [C:\copy.accdb].table1 where id=1").EOF Then
	CurrentDb.Execute "insert into [C:\copy.accdb].table1 select * from table1 where id=1"
Else
	MsgBox "record already exists"
End If

 

[bigal.nz]

Hi,

I dont think that will work as ID is a auto generated primary key and if I copy it across there could be key conflicts.

I need to copy without the key.

Al

 

[static]

It was an example.