Assuming that by 'another database' you mean a separate file, not another
table in the current database, then create a link to the table in the other
database (File | Get External Data | Link Tables on the main database menu
bar, or equivalent if using Access 2007). Then from your form, e.g. via a
command button you can execute an SQL statement to insert a row into the
linked table, e.g.
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' ensure current record is saved
Me.Dirty = False
' insert row into linked table
strSQL = "INSERT INTO [table2]([name], [soc sec no], [age]) " &_
"SELECT [name], [soc sec no], [age] " & _
"FROM [table1] " & _
"WHERE MyID = " & Me.MyID
cmd.CommandText = strSQL
cmd.Execute
where table2 is the linked table, table1 is the form's underlying table and
MyID is its primary key column of number data type, e.g. an autonumber.
However, duplicating data like this is not usually a good idea as it can
allow anomalies to creep in. If, instead of having a local table in the
other database you simply have a link to your current table, then whatever
changes you make to the data in the current table will automatically showin
the linked table in the other database.
Ken Sheridan
Stafford, England
Bob B said:I would like to trasfer some fields, name, soc sec no, age,etc from a open
record in one access data base to create a new record in another accessdata
base. How can I accomplish this?- Hide quoted text -
- Show quoted text -
a a r o n . k e m p f @ g m a i l . c o m said:You sit around and get corruption like once amonth-- and you think
that it's acceptable.
Ken Sheridan said:Assuming that by 'another database' you mean a separate file, not another
table in the current database, then create a link to the table in the other
database (File | Get External Data | Link Tables on the main database menu
bar, or equivalent if using Access 2007). Then from your form, e.g. via a
command button you can execute an SQL statement to insert a row into the
linked table, e.g.
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' ensure current record is saved
Me.Dirty = False
' insert row into linked table
strSQL = "INSERT INTO [table2]([name], [soc sec no], [age]) " & _
"SELECT [name], [soc sec no], [age] " & _
"FROM [table1] " & _
"WHERE MyID = " & Me.MyID
cmd.CommandText = strSQL
cmd.Execute
where table2 is the linked table, table1 is the form's underlying table and
MyID is its primary key column of number data type, e.g. an autonumber.
However, duplicating data like this is not usually a good idea as it can
allow anomalies to creep in. If, instead of having a local table in the
other database you simply have a link to your current table, then whatever
changes you make to the data in the current table will automatically show in
the linked table in the other database.
Ken Sheridan
Stafford, England
Bob B said:I would like to trasfer some fields, name, soc sec no, age,etc from a open
record in one access data base to create a new record in another access data
base. How can I accomplish this?
Bob B said:This is my coding:
Private Sub Command39_Click()
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.AtiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' ensure current record is saved
Me.Dirty = False
'insert row from linked table
strSQL = "INSERT INTO {HRPD_be.mdb}({SSN}, {LastName}, {FirstName},
{Age})"
& _
"SELECT{Social Security #}, {Last Name}, {First Name}, {Age}" & _
"FROM {Clients_be.mdb}" & _
"WHERE MyId = " & Me.MyID
cmd.CommandText strSQL
cmd.Execute
End Sub
I get a error: "user defind tupe not defined" at STM: cmd.AtiveConnection
=
CurrentProject.Connection
Ken Sheridan said:Assuming that by 'another database' you mean a separate file, not another
table in the current database, then create a link to the table in the
other
database (File | Get External Data | Link Tables on the main database
menu
bar, or equivalent if using Access 2007). Then from your form, e.g. via
a
command button you can execute an SQL statement to insert a row into the
linked table, e.g.
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' ensure current record is saved
Me.Dirty = False
' insert row into linked table
strSQL = "INSERT INTO [table2]([name], [soc sec no], [age]) " & _
"SELECT [name], [soc sec no], [age] " & _
"FROM [table1] " & _
"WHERE MyID = " & Me.MyID
cmd.CommandText = strSQL
cmd.Execute
where table2 is the linked table, table1 is the form's underlying table
and
MyID is its primary key column of number data type, e.g. an autonumber.
However, duplicating data like this is not usually a good idea as it can
allow anomalies to creep in. If, instead of having a local table in the
other database you simply have a link to your current table, then
whatever
changes you make to the data in the current table will automatically show
in
the linked table in the other database.
Ken Sheridan
Stafford, England
Bob B said:I would like to trasfer some fields, name, soc sec no, age,etc from a
open
record in one access data base to create a new record in another access
data
base. How can I accomplish this?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.