By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,587 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

TransferDatabase with key in place

P: n/a
I am using the Docmd.TransferDatabase to transfer part of a table into
another smaller database. The source comes from a select query that
transfers the part of the table that I need.

The problem is that I don't get the primary key at the other end of the
transfer. All the fields and data are in place, but no primary key. This
causes a problem with the database it is exported to.

Is there a workaround to this problem. I can't find any way to transfer the
primary key. Is there another way of doing this that will give me the full
information and not just the data part of it? Also, it would be nice if
relationships were transferred as well, but it is not absolutely essential.

dixie
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Include the primary key in the fields in the query. Likely, you will still
have to designate it as the primary key in the new table. Perhaps you should
create the table, define a primary key, and use an append query rather than
transfer database to add the information to it.

Larry Linson
Microsoft Access MVP

"dixie" <di****@dogmail.com> wrote in message
news:O7**************@nnrp1.ozemail.com.au...
I am using the Docmd.TransferDatabase to transfer part of a table into
another smaller database. The source comes from a select query that
transfers the part of the table that I need.

The problem is that I don't get the primary key at the other end of the
transfer. All the fields and data are in place, but no primary key. This
causes a problem with the database it is exported to.

Is there a workaround to this problem. I can't find any way to transfer the primary key. Is there another way of doing this that will give me the full information and not just the data part of it? Also, it would be nice if
relationships were transferred as well, but it is not absolutely essential.
dixie

Nov 12 '05 #2

P: n/a
Ok, then, I have the table already created in the outside database, with the
PK defined.

Then, how do I append the records onto that table from the other database.
I want to be able to do this from the original database and send data to the
table in the outside database. That is why I used DoCmd.TransferDatabase
(it was an export).

Can I export from a database and append onto a table in another database.
The way I am doing it now, it would overwrite the existing blank table with
the PK defined?

dixie

"Larry Linson" <bo*****@localhost.not> wrote in message
news:5Z*****************@nwrddc02.gnilink.net...
Include the primary key in the fields in the query. Likely, you will still
have to designate it as the primary key in the new table. Perhaps you should create the table, define a primary key, and use an append query rather than transfer database to add the information to it.

Larry Linson
Microsoft Access MVP

"dixie" <di****@dogmail.com> wrote in message
news:O7**************@nnrp1.ozemail.com.au...
I am using the Docmd.TransferDatabase to transfer part of a table into
another smaller database. The source comes from a select query that
transfers the part of the table that I need.

The problem is that I don't get the primary key at the other end of the
transfer. All the fields and data are in place, but no primary key. This causes a problem with the database it is exported to.

Is there a workaround to this problem. I can't find any way to transfer

the
primary key. Is there another way of doing this that will give me the

full
information and not just the data part of it? Also, it would be nice if
relationships were transferred as well, but it is not absolutely

essential.

dixie


Nov 12 '05 #3

P: n/a
"dixie" <di****@dogmail.com> wrote in message news:<Ck***************@nnrp1.ozemail.com.au>...
Ok, then, I have the table already created in the outside database, with the
PK defined.

Then, how do I append the records onto that table from the other database.
I want to be able to do this from the original database and send data to the
table in the outside database. That is why I used DoCmd.TransferDatabase
(it was an export).

Can I export from a database and append onto a table in another database.
The way I am doing it now, it would overwrite the existing blank table with
the PK defined?

dixie


Do you have the PK set as an autonumber in the destination database
table? I don't think you should. Then you should be able to just use
something like this:

INSERT INTO Customer ( CustomerID, FirstName, LastName, Address, City,
State, ZipCode, PhoneNo, MapscoPage ) IN 'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage
FROM Customer;
Note the IN 'full path to other database.mdb'...

If I have understood your question correctly then this should work
fine for you.
Nov 12 '05 #4

P: n/a
Thankyou very much Pieter, with a little alteration to put in my WHERE
clause, that works. Just what I was looking for.

dixie

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"dixie" <di****@dogmail.com> wrote in message

news:<Ck***************@nnrp1.ozemail.com.au>...
Ok, then, I have the table already created in the outside database, with the PK defined.

Then, how do I append the records onto that table from the other database. I want to be able to do this from the original database and send data to the table in the outside database. That is why I used DoCmd.TransferDatabase (it was an export).

Can I export from a database and append onto a table in another database. The way I am doing it now, it would overwrite the existing blank table with the PK defined?

dixie


Do you have the PK set as an autonumber in the destination database
table? I don't think you should. Then you should be able to just use
something like this:

INSERT INTO Customer ( CustomerID, FirstName, LastName, Address, City,
State, ZipCode, PhoneNo, MapscoPage ) IN 'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage
FROM Customer;
Note the IN 'full path to other database.mdb'...

If I have understood your question correctly then this should work
fine for you.

Nov 12 '05 #5

P: n/a
Pieter, is it possible to use this procedure to import fields from an
external database into the one you are working in. Sort of the reverse of
what you have showed me here.

For example, if I was working in one database and I wished to append
information from another database without leaving the one I am in.

If I do a doCmd.TransferDatabase, it will not write the information to the
table if it already exists and appends a 1 onto the name of the target
table. It seems to me that it ought to be able to work to bring information
into the database as well as to send it out of the database.

dixie

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"dixie" <di****@dogmail.com> wrote in message

news:<Ck***************@nnrp1.ozemail.com.au>...
Ok, then, I have the table already created in the outside database, with the PK defined.

Then, how do I append the records onto that table from the other database. I want to be able to do this from the original database and send data to the table in the outside database. That is why I used DoCmd.TransferDatabase (it was an export).

Can I export from a database and append onto a table in another database. The way I am doing it now, it would overwrite the existing blank table with the PK defined?

dixie


Do you have the PK set as an autonumber in the destination database
table? I don't think you should. Then you should be able to just use
something like this:

INSERT INTO Customer ( CustomerID, FirstName, LastName, Address, City,
State, ZipCode, PhoneNo, MapscoPage ) IN 'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage
FROM Customer;
Note the IN 'full path to other database.mdb'...

If I have understood your question correctly then this should work
fine for you.

Nov 12 '05 #6

P: n/a
Sorry, I should have experimented a bit before I asked that question, I
have solved it myself - first attempt!

dixie

"dixie" <di****@dogmail.com> wrote in message
news:Xt*****************@nnrp1.ozemail.com.au...
Pieter, is it possible to use this procedure to import fields from an
external database into the one you are working in. Sort of the reverse of
what you have showed me here.

For example, if I was working in one database and I wished to append
information from another database without leaving the one I am in.

If I do a doCmd.TransferDatabase, it will not write the information to the
table if it already exists and appends a 1 onto the name of the target
table. It seems to me that it ought to be able to work to bring information into the database as well as to send it out of the database.

dixie

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"dixie" <di****@dogmail.com> wrote in message news:<Ck***************@nnrp1.ozemail.com.au>...
Ok, then, I have the table already created in the outside database,
with the PK defined.

Then, how do I append the records onto that table from the other database. I want to be able to do this from the original database and send data
to
the table in the outside database. That is why I used DoCmd.TransferDatabase (it was an export).

Can I export from a database and append onto a table in another database. The way I am doing it now, it would overwrite the existing blank table with the PK defined?

dixie


Do you have the PK set as an autonumber in the destination database
table? I don't think you should. Then you should be able to just use
something like this:

INSERT INTO Customer ( CustomerID, FirstName, LastName, Address, City,
State, ZipCode, PhoneNo, MapscoPage ) IN 'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage
FROM Customer;
Note the IN 'full path to other database.mdb'...

If I have understood your question correctly then this should work
fine for you.


Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.