Solved

Insert data from a view in one access databse and insert to an empty table in another access db

Posted on 2010-11-17
11
211 Views
Last Modified: 2012-05-10
I have a view that is used to provide data on a web page. The access db is becoming to big to transport the entire db to the web server. How do I script the

Insert into table2 (values) Select * from table1

Where the tables reside in 2 different dbs?
0
Comment
Question by:BPWALKIN
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 4

Accepted Solution

by:
incerc earned 375 total points
Comment Utility
Hi,

In the database where you have the data, create the following query :

SELECT * INTO Table2 IN 'path_to_my_second_database'
FROM Table1;

(example of above path :)
SELECT * INTO Table2 IN 'C:\Documents and Settings\user\My Documents\Database2.accdb'
FROM Table1;

You didn't say which version of Access you are using, I tested this on Access 2007.
Here is the SELECT .. INTO syntax :

http://msdn.microsoft.com/en-us/library/bb208934%28v=office.12%29.aspx
0
 
LVL 6

Expert Comment

by:YohanF
Comment Utility
why dont you create the table in the other database, link it to your main database. So the actual table will reside in the main db and a link to that in the massive (slow) db.

Then when the updating time comes up, do a delete all query and add teh records.. This may be a way to get around your problem.. Give it a think!
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
Comment Utility

insert into table2 in 'c:\myfolder\otherdb.mdb'
select * from table1


0
 

Author Comment

by:BPWALKIN
Comment Utility
using incerc's solution

I get the microsoft jet database engine has stopped the process because you and another user are attempting to change the data at the same time

this is in two test datbases where no one else is connected
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you want to append records to an existing table, use "Insert into ..."
see my post at http:#a34154694

the
"select into..." is a make table query, that will create the table in the other db.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:incerc
Comment Utility
Indeed, I confirm that the "Select .. into" creates the tables. But I thought that this is better than using "insert into.." because you don't need to create manually the table.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
well, from the title of the Q. it is an 'empty table' , so the table already exists
0
 

Author Comment

by:BPWALKIN
Comment Utility
it would be better to create the table on the fly and delete each day with ne data.

What could be the possible causes of that error.
0
 

Author Comment

by:BPWALKIN
Comment Utility
my apologies I should have been clearer
0
 
LVL 4

Expert Comment

by:incerc
Comment Utility
For the error you received, please see this thread :

http://www.tek-tips.com/viewthread.cfm?qid=226588&page=1296
0
 

Author Comment

by:BPWALKIN
Comment Utility
I got it, I ran the compact and repair and it worked fine.

Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now