June 10, 2014 at 4:49 am
Hi, I’ve created 2 Access Databases that query our Oracle Database. They both work fine, however, is it possible to combine the queries from both databases into a single SQL query? The reason for 2 Access Databases is due to the 2GB size limitation, so my queries/macros had to be split, and I’m producing output of more than 100,000 unique rows in size. The 1st database produces the total all the “Customers”, whilst the 2nd database adds items specific to some of the “customer” in the 1st database. My knowledge of SQL is minimal, so any pointers/advice is very much welcome. Many thanks.
June 10, 2014 at 7:35 am
AFAIK it is not possible to create a query from one Access database to another. If I read your post correct you try to query the Access databases from SQL. Could you do it the other way around?
Import the data from Oracle into a SQL instance (instead of two Access databases). Create one Access database and link the Access tables to the SQL instance. In the Access database you can still hold the forms and macros...
June 10, 2014 at 8:08 am
Sorry if I wasn't clear enough: The 1st Access database has a number of queries and macro which interrogates our oracle database. The 2nd Access database queries the results of the 1st database, adding-in more data. What I don't know how to do is combine all the queries/macro from database 1 with the queries/macro from database 2 in one SQL query. Apologies for the confusion.
June 10, 2014 at 8:11 am
clifford.sweeney (6/10/2014)
Sorry if I wasn't clear enough: The 1st Access database has a number of queries and macro which interrogates our oracle database. The 2nd Access database queries the results of the 1st database, adding-in more data. What I don't know how to do is combine all the queries/macro from database 1 with the queries/macro from database 2 in one SQL query. Apologies for the confusion.
This all sounds way overly complicated to me. Can you just drop the Access stuff in the middle and get the data directly from Oracle to SQL Server? Can you just skip importing any data at all by directly querying Oracle? Or is the front end in Access?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 8:28 am
That's the question. Everything I do is in Access. I don't write any SQL at all. To produce a listing of all our customers, I have to write some 7 Access queries (due to the nature of how our customers are listed) to get the final answer.
If creating a SQL query that "replicates/refines" those 7 Access queries is a better way to go, I don't know how to go about that. Thank you for your patience and keep those questions/snippits coming.
June 10, 2014 at 8:50 am
clifford.sweeney (6/10/2014)
That's the question. Everything I do is in Access. I don't write any SQL at all. To produce a listing of all our customers, I have to write some 7 Access queries (due to the nature of how our customers are listed) to get the final answer.If creating a SQL query that "replicates/refines" those 7 Access queries is a better way to go, I don't know how to go about that. Thank you for your patience and keep those questions/snippits coming.
So you don't need Access but it is the only place you are comfortable writing queries? How do you access this information?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 9:05 am
Exactly. I have Access linked via ODBC to our Oracle database.
June 10, 2014 at 9:28 am
clifford.sweeney (6/10/2014)
Exactly. I have Access linked via ODBC to our Oracle database.
I understand you have Access pulling data from Oracle. But then you mention wanting to do this from sql server. The data gets consumed by some process or users somewhere. What is the application? Can you just create a procedure in Oracle to retrieve your data without all the extra manipulation externally?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply