Process To Pull Data From Oracle To Access

Jan 13, 2007

We have a user that wants to pull data from multiple oracle tables into one access table. She will probably want to run this process daily. The tables will join easy enough, but I wasn't sure what the best way to go about doing this was.
I haven't used access very much. Again, this is a member of the user community and she is wanting to do this thru access.
Ideally, if it could be automated some way, or set up in some way where the user would not have to do much more than start the process (and not have to redo any "select" statements each time). I would assume some kind of combination of links and import (maybe using a macro?), but again I know very little about access.

Any guidance will be appreciated!

View Replies


ADVERTISEMENT

Import Data And Process In Access

May 5, 2015

I have 4 csv files, that need to be imported into Access.

1. I'm building 4 linked tables so users can update the table as needed. Then run queries based on these tables to produce report. But maybe there's a better way? the file name and location might be dymatic, so anything like 'getopenfilename' would be easier instead of linked tables?

2. These data need to be clean up first to become a 'database table'. (Delete first 2 rows, delete some columns, remove duplicates etc.) I know how to do them in VBA Excel, but never used Macro in Access before.

View 2 Replies View Related

Using Microsoft Access With Oracle Data W/o Password

Dec 15, 2004

Hello to everybody,

I want to access Oracle 8.0 Data (meaning Oracle tables) with Access 2002. My problem is that I do not want to enter the username and password every time I connect to the Access db, as I want to create some queries in Access which I will export to Excel.

I do not think that this is something difficult but I can't seem to find any solution. Is there any proposal?

View 1 Replies View Related

Can You Pull Data From The Internet Into Access?

Oct 17, 2004

I would like to know if Access can pull data and information from specific websites automatically without me having to enter the data myself each day.

View 6 Replies View Related

Modules & VBA :: Process Records Without Dates First And Then Run Another Process To Split Those With Dates?

Aug 18, 2014

I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;

Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.

I want to create a table as follows;

Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.

Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.

View 14 Replies View Related

Batch Process To Open Password Protected Access Db

Jan 30, 2008

Hi All

I need to schedule the opening of a MS Access database which is password protected.

I think the best way to do this would be to use schedule a batch process to run, can anyone help with the script to create a batch proccess to only OPEN and CLOSE an MS Access database?

Thanks

View 1 Replies View Related

Forms :: How To Set Up Approval Process With ACCESS Form Features

Jun 10, 2015

I am considering the possibility of revamping the employee onboarding & off-boarding process for our organisation to make it more efficient.

Current State: We use InfoPath forms in SharePoint Servers 2010 to handle onboarding/employee change/off-boarding requests submitted by the related department. With the Workflow process, the respective manager will receive email to approve the requested intake form and once it is done, the form will be assigned to someone in the business support team for processing. Once all the required logistics are completed, we shall enter the new hire's profiles into our MS Access employee database through an employee input form.

It works well although it seems like a duplication of work since the support team has to manually input the employee information, which is already captured on the InfoPath form, into the Access database. We also need to align the field options (such as data on drop down lists) in the InfoPath form with similar field on the input form in the database whenever there are new field added or changes made on existing fields.

Future State: I wonder if there is a way in Access to set up a similar request/approval process so that we can get rid of the InfoPath form when the requester can input the employee information on a onboarding intake form in Access with data stored in a temporary table. After the request is approved and subsequently completed by the business support team, we can say, click a button and transfer the employee data from the temporary table to the employee table. Of course, some sort of validation rules should apply before the transfer takes place to ensure data integrity.

I think the major challenges would be to build an approval process within Access to provide similar notification features as from InfoPath.

I know this might be a wild thinking but just want to look for possible options to save manual work and improve the efficiency of our process.

View 1 Replies View Related

Oracle's Access

Jun 9, 2006

I donot have much information at this time, but siiting in an Oracle fusion meeting, the Oracle reps mentioned they have a product to compete with MSAccess. Of course it runs on Oracle instead. They said Oracle had to come with a competing product because to many Access databases turn into Sql Server databases.
That is about all the info. I have right now on this. Thought it an interesting subject however.

View 6 Replies View Related

Append/Update Table With Oracle Data-ADO

May 13, 2005

I am relatively new to the use of VBA and ADO to append Oracle data to a Access Table. The code below was my first attempt and it doesn't work!!

Upon your review, you can see that there are quite a few calculated fields in the table which is generally considered to be a maintenance problem. I plan to update all fields within the table that ends with "Current."

Any insight as to what the problem could be? Is there a more efficient method to append/update data within the Access db? How would the module differ if I wanted to update the fields on the table that end with "Current?"
Do I have too many calculated fields?


Sub ADO_AppendEncDetailNew()

Dim Rs As New ADODB.Recordset, connString As String
Dim cn As New ADODB.Connection, sqlEncData As String

connString = "Provider=MSDAORA.1;" & _
"User ID=wv_juilo;" & _
"Data Source=Mrr;" & _
"Password=juilo;" & _
"Persist Security Info=True"

cn.ConnectionString = connString
cn.Open connString

sqlEncData = "SELECT ep.account_id, pe.customer_no, pt.last_name, pt.first_name " & _

pt.records_no, pe.drg_no, pe.length_of_stay, pe.patient_type, pe.admit_date, pe.discharge_date,

pe.total_charge, pe.expected_payment, pe.date_billed, max(trunc(epd.payment_date)),

ep.total_payments, pe.total_payments, pe.total_charges -

sum(ep.noncovered_pt_charges + ep.noncovered_wo_charges), pe.total_charges -

sum(etd.adjustment_amount), trunc(sysdate),

ep.total_payments/pe.expected_payment

from entity_pay ep, Preview_encounter pe, encounter_transaction_details etd, patient pt,

and

ep.account_id Not In ('CTOC','VNN','VCM','VRM','VCU','LP5')
AND epd.TRANSACTION_CODE in ('68806','68807','68808','68812') AND

pe.expected_payment>0 AND pe.expected_payment - pe.total_payments>0 AND

ep.total_payments/pe.expected_payment<0.75 AND etd.transaction_code in

('4569','4575','4580','4896') and trunc(epd.date_updated) =

trunc(sysdate) - 15) GROUP BY ep.account_id, pe.encounter_no, trunc(pe.ADMIT_DATE),

trunc(pe.discharge_date), pe.date_billed, pe.total_payments, pe.total_charges,

pe.expected_payment, ep. total_payments, pe.expected_payment -

ep.total_payments,trunc(epd.payment_date), pe.expected_payment - pe.total_payments,

ep.total_payments/pe.expected_payment, pe.total_payments - ep.total_payments,

trunc(epd.date_updated), trunc(SYSDATE), pt.last_name, pt.first_name,

pt.records_no, pe.patient_type HAVING ((pe.total_charges - Sum

(etd.adjustment_amount)) - pe.expected_payment) <> 0 ORDER BY 1, 2, 10

Rs.Open sqlEncData, cn, adOpenStatic, adLockReadOnly

Do Until Rs.EOF
"INSERT INTO tbl_CustDetail

Rs.AddNew
Rs!CID_Orig = Rs!entity_pay.account_id
Rs!CID_Current = Rs!entity_pay.account_id
Rs!EncNo = Rs!Preview_encounter.Customer_No
Rs!LastName = Rs!Patient.Last_Name
Rs!FirstName = Rs!Patient.First_Name
Rs!AdmitDate = Rs!Preview_encounter.admit_date
Rs!DschDate = Rs!Preview_encounter.discharge_date
Rs!TotChgOrig = Rs!Preview_encounter.Total_Charges
Rs!TotChgCurrent = Rs!Preview_encounter.Total_Charges
Rs!Bal_AfterInsPymts = Rs!Preview_encounter.expected_payment - Rs!entity_pay.Total_Payments
Rs!Bal_AfterAllPymts = Rs!Preview_encounter.expected_payment - Rs!Preview_encounter.Total_Payments
Rs!CoveredCharges = Rs!Preview_encounter.Total_Charges - Sum(entity_pay.noncovered_ct_charges + entity_pay.noncovered_ft_charges)
Rs!CalcAllowOrig = Rs!Preview_encounter.Total_Charges - Sum(Encounter_Transaction_Details.Adjustment_Amoun t)
Rs!CalcAllowCurrent = Rs!Preview_encounter.Total_Charges - Sum(Encounter_Transaction_Details.Adjustment_Amoun t)
Rs!VarianceOrig = Rs!Preview_encounter.Expected_Reimbursment - (Preview_encounter.Total_Charges - Sum(Encounter_Transaction_Details.Adjustment_Amoun t)
Rs!VarianceCurrent = Rs!Preview_encounter.Expected_Reimbursment - (Preview_encounter.Total_Charges - Sum(Encounter_Transaction_Details.Adjustment_Amoun t)
Rs!OrigRatio = Rs!Preview_encounter.expected_payment / entity_pay.Total_Payments
Rs!RatioLatest = Rs!Preview_encounter.expected_payment / entity_pay.Total_Payments
Rs!DateIdentified = Rs!trunc(sysdate)
Rs!Date_LastPayorPymt = Rs!max(Encounter_Payment_Detail.payment_date)

Rs.Update
Set Rs = Nothing

End Sub

View 4 Replies View Related

Oracle Vs Access And Excel

Apr 20, 2006

Hi guys,

I'm just looking for some general views and opinions here and would love to know what people think; advantages/ disadvantages etc.

We have a system of spreadsheets traking various ongoing projects in our organisation which are sitting on 6 different regional servers. This poses a number of problems as you can imagine - no way of collating the data or generating reports centrally. Updates/bug fixes cannot be carried out because it would require chaning each workbook individually and so on and so on.

I am trying to make an argument to create a new Oracle based database or similar which will be held on a central server and accessed through the company's intranet.

What I'm trying to do is compile a list of the possible reasons to do this or not as the case may be. Opinions or arguments for and against would be most welcome.

Thanks
Liam

View 1 Replies View Related

MS Access/ Oracle Connections.

Jun 20, 2006

Hello Guy,

We use an Oracle base software called Trapeze and Microsoft Access sometime used as a front end to access the Trapeze oracle tables.
Due to the possible dangers of allowing the users Access, Is there a way to block the users from changing the oracle tables and only be able in MS Access to create reports and queries? Trapeze's security is antiquated and uses Oracle to validate accessibility. Since a user has to be created in Oracle, with update, delete, read, write, append rights, for Trapeze to work correctly, a user can connect directly to the Db through Access and make changes directly to the data, unless the ability to prevent them from modifying the tables and only create reports and queries that they can save.

View 1 Replies View Related

Oracle, Tables And Access

Jul 13, 2005

Dear All,

I'm trying to link to Oracle through ODBC...everyhting is set up, and I can connect and I can also see the tables..The problem is...there are just toooo many tables in the link window!!!....

Does anybody know how to find the right table you want faster??? Or how to limit the tables that are retrieved from Oracle to just the ones I desire?

I even set up the correct Schemas to just view the tables I want...but It won't work when linking it through ODBC!..All the tables appear over and over again... And the list is just tooo long.

PLEASE HELP! I'm getting crazyyyyy :confused:

Zurvy

View 1 Replies View Related

Oracle To Access Databse

Jun 21, 2007

hi...


i have created a database link between oracle and msaccess....i am trying to access the msaccess table from sql(oracle)..

when i do a
select * from msacees_table@database_link

i am getting the desired result

but when i try to acces a specific column with in the msaccess table like

select msaccess_col1 from msacees_table@database_link

its says invalid column name....

any idea why its behaving like this ???

thanks...

View 1 Replies View Related

Access/Oracle Link HELP!

Jun 28, 2004

I was hired to write some reports in Access 2000 for a small company (max of 6 users at a time). The database was created by someone else and he decided that moving to Oracle was the answer to some of their issues (losing data due to record locking). The guy did not know Access at all so that DB is screwed up (another issue all together). I had no problem creating the reports and they ran fine on my home and work computer. When I imported the reports into the Access DB at this company they ran fine (but VERY slow). I then got a call 3 days later from them saying the reports wouldn’t run. The owner decided to upgrade to Access 2003 to see if that would help but that created another set of new error messages. When I try to run the report it gives two messages: "can not perform action/make changes in record primary key" and "record not found". These reports are based on simple select queries. Trying to run the reports freezes the application. I even tried to open the tables and it either freezes the app or takes about 15 minutes to open. These tables are linked to Oracle 8i. The newest OBDC drivers have been installed and the newest Oracle 8I edition has been installed. Since I can run the reports in Access I assume the issue is the link with Oracle. I have very little Oracle knowledge. From what I can tell the data is stored in Oracle and Access pulls the data via the links. Has anyone experienced this? Does anyone know how to fix the issue? Your help is very much appreciated!

View 4 Replies View Related

Transfer Access-Queries To Oracle

May 26, 2005

Hi,

I'd like to transfer access-queries to Oracle. If I export access-queries, Oracle recognize these as tables and not as dynamic queries? Any idea how to solve this or does anybody know a tool to create queris in Oracle?

Thank!
Tom

View 2 Replies View Related

Importing Records From Oracle To Access

Mar 22, 2007

I'm using the following tools/systems:

* Oracle 9i Enterprise Edition Release 9.2.0.5.0
* Microsoft Access 2002
* Access is running on a XP Professional desktop

What I'm doing at the moment is:

* Linking the Oracle tables in Access via ODBC
* Using Access to run develop & run queries against the linked Oracle tables

What I'm trying to figure out:

* What is the easiest way to export the query results to a local (on my machine) Access table?

Some of the queries return < 65000 rows, so I can export to excel & import the excel file back into access. Unfortunately this doesn't work for the bigger queries.

Suggestions?

View 5 Replies View Related

Access Database Migration To Oracle

May 10, 2005

Hi, I have a question regarding improving the performance of an Access front-end linking to Oracle tables.

Basically what I have done so far is migrate around 35 or so tables into an Oracle 9i database. After linking the tables in Access and prototyping some of the existing forms/reports/queries in Access, I noticed that the speed performance of everything was noticeably slower. Any suggestions as to how I can resolve this issue? For read online queries and reports, I understand that I can use a pass-through query to speed things up. However, all the forms need to allow for data entry and based on my understanding, the pass-through query solution would NOT work for this.

Any suggestions? Any help would be appreciated.

View 4 Replies View Related

Tables :: Using Oracle As Back-end For Access?

Apr 23, 2013

I have written a few solutions in Access (using access 2007). Now I going to create a few new ones for my company. We use Oracle for our main database and was wondering what would be the advantages and disadvantages of using Oracle as my backend while Access is my front end?

Also, should I create the queries in Access or in Oracle?

finally, is there anything I need to do differently?

[URL]

View 7 Replies View Related

General :: Ms Access Linked To Oracle

Mar 10, 2014

I have linked ms access to orcale to run a query, the date in orcale is in this format 20140101, i have tried to convert the date in the query like that but it's not working. format(mydate,"yyyy/mm/dd".

View 3 Replies View Related

Link To Both Oracle Tables & Access Backend

Nov 30, 2006

Is it possible in access to link to both oracle AND access tables in the same
access application?

View 2 Replies View Related

Access 97 + Linked Table Oracle + Autonumber

Mar 24, 2006

Hello,

I'm making a MS Access frontend for some tables on the Oracle 8 database at work. The tables are linked ofcourse.

One table has an AUTONUMBER field on the Oracle and it seems to give me trouble to insert new records.
When I try to insert a new record (leave the autonumber field blank) I get the following error: "ODBC — insert on a linked table <table> failed. (Error 3155)" followed by the error "[Microsoft][ODBC Driver for Oracle][Oracle]ORA-01722: invalid number (#1722)".

When I look at the Oracle documentation I got this:
ORA-01722 invalid number
Cause: The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that
they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
I checked the INSERT statement: "INSERT INTO AFM_HV_PROP_VALUE (HV_INST_ID, HV_PROPERTY_ID, TABLE_NAME, HV_PROPERTY_VALUE) VALUES (4, 'V_TESTJE', 'hv_inst', '123465')" and everything seems to be allright. The value that's causing the error is the "4" that gets in the "HV_INST_ID"-field.
Using TOAD to execute the SQL Statement, there is no problem at all.

When I look at the table design in MS Access I see that the Autonumber field is of the type "Double". That doesn't seem right to me...

Anyone some suggestions? I'm running out of courage :s

Greetings,
Niels R.

View 1 Replies View Related

Ms Access Converting Oracle's Number Field To Text

Jul 5, 2007

Hi,
Can anybody help me ?
I've a case where ms access displays oracle's numeric field as text where msaccess is linked to oracle.The possible reason could be when we don't specify precision in declaring the filed in oracle table, default precision is 38 which ms access cannot hold. So, it(driver) converts this to text field.But this is with MSORCL32.dll(microsoft driver for oracle).Is there any other possible reason that will result this behavior?

The following are the configuration details:
1.Oracle 9i on Unix server.
2.MSAccess on Windows XP.
MSAccess holds linked data of Oracle server.

Thanks Inadvance.
Ramesh

View 4 Replies View Related

Join Syntax Errors Translating Oracle SQL To Access SQL

Mar 21, 2008

I've developed a working query to grab some information from my Oracle 9i database using SQL Developer. I have a tool in MS Access 2003 that I'm developing for other users so they can input some options through the GUI and query the same data that I do.

I'm trying to translate the query I developed in Oracle and have it work in Access. I have my ODBC connection set up so that Access can get to the same tables.

Here is my Oracle Query:

[QUERY]...

The error I'm receiving is: Run-time error '3296'

Join expression not support.

I believe the error has something to do with the "LEFT JOIN CPCF ... " but I can't seem to figure out how to fix it. The query will run in Access if I change that to an "INNER JOIN CPCF ...." but the result set is not what I need it to be. Any thoughts on how to fix this?

View 1 Replies View Related

Modules & VBA :: Transfer Database From Oracle Into External Access Db

Oct 26, 2013

i'm running a module from an access db. how do i import a table from oracle into a closed access db, not the one where the code is running from?

View 1 Replies View Related

Avoiding DB Login Screen Everytime We Run MS Access DB Queries Which Have Oracle DB

Oct 9, 2006

Hi -

Currently I have MS Access DB which has some Oracle database table reference using database link concept. I have entered DSN Name,user id and password which it asks when I try to run some queries which will fetch data from oralce DB linked tables.If some wants to use my MS Access DB, they need to have DSN and everytime they run queries related to Oracle database table reference, it will ask for DSN,user id and password. For this I need to share the user id and password credentials to everyone who wishes to use my MS Access DB which I don't want to. Is there a way to avoid the login window whenever some one wants to use my MS Access DB without asking for oracle user id and password credentials everytime? My ultimate aim would be encrypt password for others.

Thanks,
Ashok

View 4 Replies View Related

Updating Oracle Backend Tables From MS Access Forms(dynamically)??

Feb 13, 2007

Hello,

My requirement is this...

I have to use MS Access as front end with ODBC connection to Oracle 9i

DB.

The application(forms) should be able to update, delete ,insert records into oracle tables(backend).

i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to
1) search for the unique id in the main form such that the subform displays all its matches
2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW
DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE???
similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table.

the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted..

How should i do this?? i am new to MS access .VBA, any help would be highly appreciated!

Thanks so much!

View 1 Replies View Related







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