Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

MS Access: Transfering table data from old db to new tables in new db

I am ready to insert data from the old db into the new db. I know that I should use a query. I am thinking that I would simply use a “select” statement selecting each field of the old table that I want and then use an “Insert Into” each field of the new table where I want the info to go to.

I will have to select data from the tables “Tool List”, “Inspection Log” and “Dimension Verification” of the old db and Insert Into the tables “tblTools”, “tblInspections” and “tblDimensionVerification” of the new db.

These are the only tables that I will have to deal with because the project in the old db never went that far in development.

Do I do all three tables at the same time? Or start with the “Tool List” & “tblTools” and then do the other tables one at a time?

It looks like I will have to write out long hand the SQL as the query wizard doesn’t seem to cover this.

Any suggestions?



Instruments5-11-11.mdb
0
James Coats
Asked:
James Coats
  • 5
  • 2
1 Solution
 
peter57rCommented:
Have the designs of the tables changed?  If not just import the tables into the new database.
0
 
James CoatsAuthor Commented:
Peter,

The designs have changed a little. This is the SQL I have been trying to use to move the data:

SELECT [Tool List].[Description of Instrument], [Tool List].[Type of Device], [Tool List].Location, [Tool List].Brand, [Tool List].[Code #], [Tool List].[Model #], [Tool List].[Serial #]
FROM [Tool List], tblTools;

INSERT INTO

[tblTools].[ToolName], [tblTools].[TypeOfDevice], [tblTools].[Brand], [tblTools].[Code_#], [tblTools].[Model_#], [tblTools].[Serial_#],

It is not working. Could I just copy and past into the parent table (tblTools) and then just do the same with the child tables?


0
 
James CoatsAuthor Commented:
I have imported the old tables into the new db just have not moved the data yet
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
peter57rCommented:
You append query is incorrect- you have not a assigned a target field for Location.
0
 
James CoatsAuthor Commented:
OK I corrected that with this but I still get an error: Which says "characters at end of SQL Statement"

SELECT [Tool List].[Description of Instrument], [Tool List].[Type of Device], [Tool List].Location, [Tool List].Brand, [Tool List].[Code #], [Tool List].[Model #], [Tool List].[Serial #]
FROM [Tool List], tblTools;

INSERT INTO

[tblTools].[ToolName], [tblTools].[TypeOfDevice], [tblTools].[Location],  [tblTools].[Brand], [tblTools].[Code_#], [tblTools].[Model_#], [tblTools].[Serial_#],

0
 
James CoatsAuthor Commented:
Thanks Peter57!!!
0
 
James CoatsAuthor Commented:
OK guys,

Here is the finished db or well almost. The db functions great and I hope that someone on EE can use it given that many helped to develop it I thought it should be here so everyone can have access to it.

All the queries work but I still have to make pages for them. I may still need some advice and assistance in this area but right now we are using the db to check instruments and it is serving us well.

Thanks again to everyone.
InstrumentsJamesCopy.zip
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now