Query From 2 Different Databases

May 20, 2005

I have 2 different databases. One for Archives and one for active claims. Both databases have identical tables. I'd like to create a query that will pull information from both databases. I'm pretty sure that I can do this with a union query, but I'm a complete noob with sql.

Can someone give me an example of the sql code to use to pull information from tables on both databases?

View Replies


ADVERTISEMENT

Update Query Between 2 Databases

Aug 20, 2007

Hi
I have a problem with Update Query, I have 2 DB's and need to update a table held in DB#1 into a table in DB#2. I have read quite alot of post's about this but nothing is giving me the correct answer. I tried using Append Query but this was no good because when i ran the query it just adds the extra entries, I want the query to Update the entries already in DB#1. I also tried Update Query but didn't get the option to choose another DB.

Could anyone please tell me the best way to extract the data from tblInstruments(DB#2) and update tblInstruments(DB#1) i.e. overwrite the original field entries.

Many thanks.:confused::confused::confused:

View 12 Replies View Related

Query To Merge Two Databases Into One

Nov 21, 2007

Dear all
I need to merge a number of databases of the same type into one database in access. can you please help me with code itself.
thanks.

Ampah

View 2 Replies View Related

Query Won't Run Off Of Identical Databases

Sep 23, 2004

I have a query that is based on two other queries (they feed a larger query that a report uses). They are all select queries, no make-table, etc.

The query runs fine against the production oracle database. However when the query is run off of either of the two identical test databases (production was dumped into test within the last week), the query stops with an error of Invalid Operation.

The Help Screen (see below) doesn't seem to apply to anything this query is doing. Any thoughts?

Invalid operation. (Error 3219)
Possible causes:

You tried to write to a read-only property. See the Help topic for the property to determine whether it is read/write.
You tried to use a method or property on a type of Recordset object that the method or property does not apply to. See the Recordset object summary topic to determine which methods and properties apply to a given type of Recordset object.
You tried to append a property to a Properties collection of an object that does not support user-defined properties.
You tried to use the Update method on a read-only Recordset object.

Thanks,
B

View 2 Replies View Related

Create Query Over 2 Databases

Feb 15, 2007

Hi all,

Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.

Any help would be greatly appreciated.

View 1 Replies View Related

Processing A Single Query In Two Databases

Nov 7, 2006

I have two identical databases in MS Access. I have created a query in the first mdb file, which retrieves the matching records in two tables.

My problem is that i want to execute the same query created in the database1, on database2, without creating the query in the database2.

I think it is possible, but the query i was trying was not working file.

select * from db1.[Query1] in 'C:DBDB2.mdb'

where db1 is the name of the first database,
db2 is the name of the second database and
Query1 is the name of the query in the db1.

Thanks in advance for the help.

View 1 Replies View Related

New To Databases: I Need Some Advice

Jun 2, 2005

I'm creating a database that keeps track of printing jobs at a printing company... I started my project by drawing out how I want the databases to be configured.

I was going through a book that was made for access 2000, but I need to create this in access 97 because that's what the company has on their computers. One of the features in Access 2000 thats not in 97 is subdatasheets...

Basically, what I want to do is for each printing job, there can be a bunch of different tasks that need to be completed and billed for. For example, on one printing job, they need to design a logo, and then they need to print it out and send samples across the globe, and then they need to create a pdf, etc. This is going to be different for each job.

What I figured I would do is create a separate table to take care of all of the different tasks that are related to each job. This table would have the primary key of the job from the main table for each individual job, and then they would be related in a one (MAIN entry) to many (tasks) relationship.

Is this correct in how I want to do that? How will I do this inside a form, I want them to enter the information in table that expands as they put more tasks in?

This might be a very simple question, I just want to know if I'm going in the right direction.

Thanks much!

View 2 Replies View Related

MDE/Splitting Databases

Jul 14, 2005

Hello,

I have been reading a lot about splitting databases on this forum. I still have some questions.
1) Will the FE (Front End) still show the tables?
2) Will users still be able to edit the forms, reports, etc.?
3) Will my code be hidden
4) Will all the users have up to date data showing when they open the Database?
5) Can more than one person open and input data in the database at the same time?

I also want to make an MDE copy, do I split first or make the MDE and then split?

The whole point is the following: I want the people (maximum 10) that will be using this database to only be able to do enter and view data. They should be able to generate the reports but not create new reports. I only want ONE person to be able to edit the forms, code, and reports. How would I do this.

Your help is much appreciated

View 3 Replies View Related

Read Only Databases

Aug 9, 2005

Forgive me if this is in the wrong forum, so MODS, do what you will.

I'll come out and admit it right now, I'm a newbie here and have just started using Access2003. I'm in a city government so I need other users to be able to view and access my database, BUT I don't want them to be able to edit or change things. I want to be the Owner/Administrator of the database and no one else. I want to be the only person that can make changes.

Is this possible in Access 2003? Can other users only open it in Read-Only format but when I open it, it's editable?

Any info would be greatly appreciated. TIA!

--Adam

View 2 Replies View Related

Merging Databases

Aug 24, 2005

I am new to Acess, I am trying to merge 2 Access DBs and am not sure of the steps to accomplish this, if it can be accomplished and/or are there any tools to reach the same goal.

View 5 Replies View Related

Distributed Databases

Nov 28, 2005

Hi,

I have an MS Access database on a server which acts as a back end, with about 30 users using the data. They each have a copy of the MS Access front end located on their PC's.

Every time I make a change to the front end, I have to redistribute the new version to each PC.

What I am wanting to do is put a single copy of the front end on the server, and adding a shortcut to this front end on everyone's PC's. This would allow me to make changes to the front end without having to redistribute to all users.

What I was wondering is, if I put a single copy of the Front end on the server and simply shortcut to that front end, will it cause any problems, or are there any issues that I should look out for?

Any help or advice much appreciated!

View 2 Replies View Related

Linking Databases

Dec 15, 2005

I have a Form that refers to two Reports, both of which refer to two query's one for each. the whole lot(1 Form, 2 Reports, 2 Querys) is in one mdb file and the actual database is another mdb file. How do I get my objects to get their data from the main Data Base File? the 5 objects are acting as a small report making application.

View 5 Replies View Related

Linking Databases

Jan 11, 2006

:confused: Does anyone know if there is any way to link to seperate databases so that any information entered in one will update the same information in the other and visevirsa?

Thanks

View 3 Replies View Related

Integrating 2 Databases

Jan 19, 2006

My company has a database. I downloaded a copy of it to my desktop to work on it. Can I integrate my changes to the original? (copy/paste,export etc) I hope so.

View 2 Replies View Related

Three Databases One Switchboard

Oct 12, 2006

I have three different databases, engineering, properties, and my contacts.
I would like to have one switchboard that I can put on my desktop to access all three databases. Is this possible?

Any help would be greatly appreciated! :)

View 5 Replies View Related

Network Databases

Oct 20, 2006

Just trying to get some more replies...

This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!

I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk.
The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.

Would really apopreciate any thoughts anyone has..

Thanks a lot.

View 10 Replies View Related

How To Merge Two Databases To One

Nov 12, 2006

I have a database with two tables, Buyer and Products, with one-to-many relationship. The key of Buyer is an autonumber ID, which is the foreign key to table Products.

The problem is that i have two copies of this database with different records (with ID starting from 1 to each one) and i want to merge the databases into one.

Any ideas how to do this without lose the connection of the records?

Thank you in advance.

View 2 Replies View Related

Help How Do I Merge Two Databases????

Mar 30, 2007

Hi,

How do I merge these two datbases? I have downloaded them from Microsoft online templates, here
http://office.microsoft.com/en-gb/templates/TC010178391033.aspx?CategoryID=CT101426031033&av=ZAC000
and here
http://office.microsoft.com/en-gb/templates/TC010185481033.aspx?CategoryID=CT101426031033&av=ZAC000
and I want to have the two databases as one so that there will be a fourth tab on the cintact management database with the order form on? I want to be able to link the two so I can have all the customers details and orders together and print off reports. I really have not dealt with Access before so I really dont have a clue. Any help would be truly appreciated. I am sure it is easy but I just cant work out how to do it.

Thanks very much

Danny :)

View 3 Replies View Related

Merging Databases

Oct 25, 2007

Hello-

We are using an Access database for off-site data entry. We therefore have one master database we keep in the office and then a "template" version -which is identical to the master version except no records - we bring to each site, do the data entry and then merge that data back into the Master database.

Can anyone help suggest ways to make the data merging process more efficient. The database has approx 12 tables, so I dont' want to create an append query for each one every time. Is there a way to just "merge" two identical databases??

THANKS

View 7 Replies View Related

Split Databases

Feb 21, 2008

Hi,

I just got Access 2007 and split a database into a front and back end to see what it was like.

Is there any way of joining them back again?

Thanks

David :o

View 1 Replies View Related

Having A Big Problem With Databases

Mar 5, 2008

hi all!
i have recently been set a sort of project to do and i have to create a database that is for booking customers into a hair dressing salon, this is not for a real company, it is made up but i have to do it and i don't have a clue how :(
i need a customer form with all their data on, this can be random
a booking form that i can book the customers into different treatments
and this is the hard part i need a staff form and some how i need to know what days they work on so that it will come up on the booking form so that you can search for available days, its really hard and im really stuck, can anyone Please help :confused:
hope i hav'nt confused anyone

View 2 Replies View Related

I've Always Been Able To Fix Corrupt Databases Until Now...

Nov 6, 2006

Hi all:
Would appreciate any help that anyone can offer. Have a client with an Access2K database that gives the dreaded "...No read permissions on table MSysObjects..." error. I'm trying to just get the data into a new clean mdb but Access blocks me at every turn.

Have tried compact & Repair
Have tried JetComp
Have tried TransferDatabase via code
No luck, can't get at the data.

Now here's the kicker, the database is the back end to a VB6 application. The application continues to run fine and is happily accessing and updating the database. Any suggestions about alternate ways to get the data into a new clean mdb would be much appreciated.

TIA

John

View 4 Replies View Related

Querying Two Databases

Feb 15, 2007

Hi all,

Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.

Any help would be greatly appreciated.

View 2 Replies View Related

Comparing Two Databases

Apr 10, 2007

Hi

what i want is, if i update one database to new database then
i want to compare tables in these two databases if any table is not matched it should be retrieved,so please tel me the solution for this.

View 3 Replies View Related

Linking Databases

Jun 28, 2006

Hey all!

Okay, so here's a real puzzle, so put on yur thinking caps!

I have two large databases that are twins of eachother, with only a minor difference in a small corner of one table. Even the forms are exactly the same.

I want to split each one from thier tables, so I have databases strictly for [tables], and strictly [forms, queries and reports]...

Then I would like to get rid of one of the copy cat [forms, queries and reports] db's leaving a single [f,q and rpts] db with the ability of ANY user (not a semi-skilled db dev such as me) to have the option at opening, which db to link to for the tables (without going into the tool bar, since it is not visible to them).

Is there an EASY way for a "non-dev" user to choose which [tables db] on an opening form/switchboard?

Live long....prosper.

View 3 Replies View Related

Linked Databases

Sep 21, 2004

Hi
I have an access database which is linked to several tables in other SQL databases. I have made a list box displaying a single field from one of the linked tables. I have the list set to multiselect simple. After I've made a selection and then go to close the form I get this error message.

ODBC - Insert on a linked table 'dbo_Name" failed.

I dont want to change any data in this table. I only want to read and extract it to another table. How can I stop access from wanting to update the table.

OK I've thought about it some more, can I as part of the on click commands for the close button, clear any selections from the list prior to closing the form. That way there are no fields selected to be changed and no error message. If I can any pointers on how?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved