Hi guys

Get ya teeth into this one.

Is it possible to transfer a complete record from an MS Access database to another identical MS Access database without having to transfer each field to a variable then adding a new record and transferring the variables into the new record.

What I'm really after is some smart bit of coding or SQL that can transfer/copy a record quickly and quietly.

Regards

Shady


____________________________
I don't wanna die... but I ain't keen on livin' either

hello, shady.

what am i to assume? that you want to use vb, or that you want to use access?

i just want to have a clearer idea of what's in your mind.  knock ,knock

____________________________
Been busy trying to take a second degree <--it's not working out...

Well, if you wanted to use a SQL statement it'd be like:

[For Access or VB]

INSERT INTO Table2 SELECT * FROM Table1 WHERE ID='unique value' ' Or for all info (copy the table) INSERT INTO Table2 SELECT * FROM Table1

[For VB]
Although if you use a recordset you still don't need any additional variables

' Where rs1=first table, rs2=second table     Dim i as Integer          Do         rs2.AddNew ' Add new         For i = 0 to rs1.Fields.Count ' Insert all data             rs2.Fields(i) = rs1.Fields(i).Value         Next         rs2.Update ' Save current record         rs1.MoveNext ' Move to next     Loop Until rs1.Eof

[Edited by JLRodgers on 04-12-2002 at 12:43 PM GMT]

OK,

So we have an SQL statement that will perform the task I require.  So how exactly would I use this SQL in VB.

Is it Data1.recordsource = SQLStatement

or

ExecuteSQL SQLStatement

or

something else.

Sorry to be a pain, I just haven't used SQL that much

Thanks

Shady

____________________________
I don't wanna die... but I ain't keen on livin' either

It would be used like:

For a connection in VB (where cn is the variable):
cn.Execute SQLStatement

OK,

So this is where I become really annoying and make myself look a complete ignoranmus.

Having not used SQL much I have never used the connection method. I also get rather confused between DAO and ADO. I used VB6 and have always used the DATA1 object to access my databases.

I have seen many bits of code using:-

Dim Cn as Connection
Set Cn = ADO.Connection

(NB I know that may be completely wrong!!)

So if I have 2 databases one called db1 and the other called db2, one has a table called TEST1 and the other TEST2, there is only one field called... wait for it... FIELD1 and the record I wish to copy has "1234567890" as its value, how (using Data1.[whatever]) do I set a connection and then execute an SQL statement?

Cheers

Shady

____________________________
I don't wanna die... but I ain't keen on livin' either

Private Sub Form_Load() ' commented adcDB2 lines aren't needed, but are listed ' so you can open the other database if you wish     Dim adcDB1 As New ADODB.Connection '    Dim adcDB2 As New ADODB.Connection     Dim sSQL As String          Set adcDB1 = New ADODB.Connection '    Set adcDB2 = New ADODB.Connection          adcDB1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "db1.mdb;Persist Security Info=False" '    adcDB2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path & "db2.mdb;Persist Security Info=False"      ' Note, the db2.mdb below needs to be the complete path! ' Field1='1234567890' if the field is text, ' If number then it's Field1=1234567890     sSQL = "INSERT INTO Test2([Field2]) IN 'db2.mdb' " & _             "SELECT Field1 AS Field2 " & _             "FROM Test1 " & _             "WHERE Field1='1234567890'"     adcDB1.Execute sSQL          adcDB1.Close '    adcDB2.Close          Set adcDB1 = Nothing '    Set adcDB2 = Nothing End Sub

There's also a relevant link at http://www.andreavb.com/forum/viewtopic.php?TopicID=378#984

It's suggested by the MSDN to use a linked table in the first database, then inserting the values that way (same query, just without the "IN 'db2.mdb'").


[Edited by JLRodgers on 06-12-2002 at 02:30 PM GMT]

OK,

Once again I require a little help here.

I tried pasting the code into a form, using two data controls and datagrids so I could see the databases.  I made the necessary alterations to the code you supplied, and in just the first few lines I got errors.


' commented adcDB2 lines aren't needed, but are listed
' so you can open the other database if you wish
Dim adcDB1 As New ADODB.Connection
'    Dim adcDB2 As New ADODB.Connection
Dim sSQL As String
    
    Set adcDB1 = New ADODB.Connection
'    Set adcDB2 = New ADODB.Connection
    
For instance New ADODB.Connection is not recognised, it tells me 'User-defined type not defined'.  And Set adcDB1 as New ADODB.Connection tells me 'Type mismatch'.  Does this have anything to do with me using VB6 and the code above only working for .NET or do I need to declare user-defined CLASS or what?

Sorry to be a pain.

All your help is much appreciated.

Regards

Shady

____________________________
I don't wanna die... but I ain't keen on livin' either

You need to add references to the Microsoft ActiveX Data Objects (the newest on your PC)


____________________________
Everywhere's Local (classifieds, job postings, & more for everycity in the world - user entered)