Updating backend oracle tables from MS Access

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 have attached a sample of how the main and sub forms should be and if i add check box next to match id and if they select (or check)one row, how to capture that particular row's data???

i am new to MS access VBA, any help would be highly appreciated!


Thanks so much!


Post your answer or comment

comments powered by Disqus
--------------------------------------------------------------------------------

I had a project that was using SQL, and then had to modify it to put a date into a field, going from MS Access to Oracle. Here is what the code looked like:

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, Description, FSCM, "
strSQL = strSQL & "ATAPN, ESDS, SPN, "
strSQL = strSQL & "Keyword, CompVal, DimData, ModUser, ModDate) "
strSQL = strSQL & "VALUES ('" & rsTemp!PN & "', '" & strDescription & "', '" & rsTemp!Fscm & "', "
strSQL = strSQL & "'" & strATAPN & "', " & intESDS & ", '" & strSPN & "', "
strSQL = strSQL & "'" & rsTemp!Keyword & "', "
strSQL = strSQL & "'" & rsTemp!CompVal & "', '" & rsTemp!DimData & "', "
strSQL = strSQL & "'SYSTEM'" & Now()
conOracleDb.Execute strSQL, , adExecuteNoRecords

This did not place a date into the desired field. So, I deleted that piece of code and put in the following code:

strSQL = ""
strSQL = "SELECT * From MasterPN"
rsPart.Open strSQL, conOracleDb, adOpenStatic, adLockOptimistic, adCmdText
If Not (rsTemp.EOF = True And rsTemp.BOF = True) Then
rsPart.MoveLast
rsPart.AddNew
rsPart!PN = rsTemp!PN
rsPart!Description = rsTemp!Description
rsPart!Fscm = rsTemp!Fscm
rsPart!ATAPN = rsTemp!ATAPN
rsPart!ESDS = rsTemp!ESDS
rsPart!SPN = rsTemp!SPN
rsPart!Keyword = rsTemp!Keyword
rsPart!CompVal = rsTemp!CompVal
rsPart!DimData = rsTemp!DimData
rsPart!ModUser = "SYSTEM"
rsPart!ModDate = Now()
rsPart.Update
End If

And that code worked!! My question...... can anyone tell me why one way doesn't work but another way using the same Now() function does work? This one drove me nuts for the better part of a day.

Are there issues between MS Access date functions and date items in Oracle?

Any help and advice on this will be greatly appreciated. Thanks!!

hi,

Currently i know how to extract info from 1 table in MS Access into an excel spreadsheet. However, when i do that, Ms excel will create a new spreadsheet(page) for that table. This is a good way to differentiate tables but for me i need a different method.


How do i extract the info i want into a single spreadsheet? This is because my tables are interlinked and i want to show the info across a single spreadsheet.

I have just imported my database tables into an Ms SQL Server database using Microsoft SQL Enterprise Manager.

I can see all my tables and their contents.
If I now try and link to these tables from Microsoft Access 2003 the tables are listed in the following format:

username.table_name

Consiquently linking fails as the table name in this format cannot be found.
I can connect to these tables sucessfully using Excel and the tables list correctly as table_name only.

Why does Ms Access think the tables are named username.table_name and how can I overcome this?

hi,

Currently i know how to extract info from 1 table in MS Access into an excel spreadsheet. However, when i do that, Ms excel will create a new spreadsheet(page) for that table.


How do i extract the info i want into a single spreadsheet? This is because my tables are interlinked and i want to show the info across a single spreadsheet.

Hi !

I'm a newbie in ms access.
I have an access database that is getting larger and I have limited time to restructure the database and put it in sql server.
I need to buy time to increase the size of my access database.
What I'm thinking is that I want to migrate my access database to sql express.
Then, I want to be able to access my table in sql express from my microsoft access form.
Is there some kind of tutorial or procedure that need to be done to read data from sql express from access form?
Is there some kind of tutorial to migrate data from ms access to sql express in a fast manner?
Thanks

Does anyone know how to import tables from another access database file, by using code? What I'm looking for is code for a button, which will let you browse for a specific file. When file is chosen, it should show path in a text box, and after you click on an 'update' button, all tables from that access database file, should be imported into the current one. The imported tables, should just overwrite existing ones, in case names are identical.

Appreciate any help you can give, as I'm quite lost.

Hi,
I have a set of forms in my MS access database and have linked it to oracle database with ODBC. With the link tables now available , could someone help me out as to how to write VBA code for the buttons so as to retrieve info from the oracle database ?

I would be happy if you could provide a sample VBA code for this querry i.e. when the student id is selected through drop down list and Search button is clicked it must go and look up in the table in oracle and must give all the students details .

Thanks in advance for the help

Hi:

I have migrated MS Access table from .mdb to MSsql server, from my computer and my administrator's pc can run the new Access application without any problem, but another user just can't run this application (before the migration, she runs the .mdb without problem).

error: The Expression After update you entered as the event property setting produced the following error: ODBC-connection to "testSQLDB" failed.

I am worry about my connection string is not right:

The connection string before was: "Provider=Microsoft.Jet.OLEDB.4.0; data source=serversharedProcessingdbaseProcessing .mdb"

The connection string after tables moved to sql: "Provider=SQLOLEDB; Data Source=SERVER; Initial Catalog= VisionSQL;Integrated Security=SSPI;"

But if that is wrong, my pc and my administrator's pc can't run also (in fact, I am using the server pc, so I am the owner, administrator he has the role of administrator).

So, it must be a permission issue or missing something, (the previsou .mdb in "Share" location, and everybody can run that .mdb before, the newly installed sql database is in C drive by default).

What will be the most likely problem? Do I need to check if the user's MS Access "Data Source Name" has the database name, or create New Data Source?


Thanks in advance.

Jt

so i have this string:


	Code:
	               
                          strSQL = "INSERT INTO tbl_school " & _
                                   "(school_name, school_degree, school_major, school_startdate, school_enddate) SELECT
(school_name, school_degree, " _
                                 & "school_major, school_startdate, school_enddate) FROM tmptbl_school;"
                          .Execute strSQL, , adCmdText + adExecuteNoRecords
                          strSQL = "SELECT Last_Insert_ID();"
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastSchoolID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With

the thing is tmptbl_school is wihtin the ms access front end. so it keeps telling me that the table does not exist which makes a lot of sense, does anyone know how to correct my syntax?

Hi,
I am currently migrating an database application from MS-Access to MSSQL Server 2005.

Details of MS-Access application
Certain VB Forms has been designed within the MS-Access database. They wil be talking to some tables in the MS-Access DB and make necessary updates.

What is that I have done so far?
I have now migrated all the data present inside MS-Access(Tables + data) to MSSQL 2005 Server. Created a VB6 application and designed the similar forums. I have made the Database connection using DataEnvironment variable and checked the connectivity to MSSQL 2005 Server DB. The Connection is fine. I am able to connect from VB6 to MSSQL 2005 Server. I copy pasted the entire piece of code from each form in MS-Access to the form in MSSQL 2005 Server. I guess this is NOT THE RIGHT WAY to do it. Not sure I tried to EXECUTE the VB6 application and getting the error in the line in the 1st form that would load Private Sub Form_Load()
Application.CommandBars("Menu Bar").Enabled = False ------ In MS-Access when I type in Application. the set of Options which can be used are displayed. But when I type Application. in VB6 nothing happens. VB6 is NOT ABLE to RECOGNISE this Application.

I want the EQUIVALENT of Application. in VB6

Since I haven't worked on VB before, I am not very sure how to proceed further. Can somebody HELP ME here?
WHAT TO DO NEXT ?

Regards
Kamath

I have a table in MS Access 2010 with a Date field and 3 Memo fields. I wish to append data to the Access table from a MS Word doc that has a date field and 3 memo fields. My wish is to have a command button on the MS Word doc which appends the data to my Access Table. Can it be done? If yes, please direct me to a tutorial that explains this.
I am aware that a web page can append data to an Access table. Thanks

Hi,

I have a question.

Can MS Access table link to Outlook?

Suppose there are Tom and Sue persons:

Sue manage the acc.mdb database file, The database has one table which is called "input", Sue made the form, the form is come from "input" table info. and also Sue made one button to send the specific record to the Tom via outlook, once Sue send out, the mail has created a link, which link can open the document (NOT attached file). Once Tom receive the mail from Sue, Tom will click the link to open the document, and also fill out one signature inside the document, after filled it out, then the signature will automatically populate in the specific field of the "input" table in the acc.mdb database file.

Can MS Access and Outlook do above situation?

Please let me know, thanks a lot.

Hi All,
Hope you are doing well.
I need help to send email from MS Access as I have millions of vendors to whom I need to send email notifications when payment has been released to them through email.

Conditions:

1. Each vendor has multiple records of consultants to be selected from MS access.
2. Email needs to be send to vendor pulling record from Vendor list table with Specific subject line identified for that vendor
3. Emails should attach excel file while sending email.
4. Excel file should include only record pertaining to that vendor to whom email will be sent.
5. If email was not able to send vendor, then need record for those vendor.
Your help in this matter will be highly appreciated.
P.s. I don’t have technical knowledge of VBA or Macros.
Thank you.

I’ve read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel.

Problem I’m having is that I don’t know which one would work best for me, so I thought since I’ve gotten such great advice from this form I will ask.

I have a query that updates a table in access.
What I would like to have happen in the export from access to excel

Currently I have a number of queries that updates number of table daily.
Then I manual export the tables to a excel files
Then I spend 15 -20 minutes formatting the excel fine. “I format all the table to excel files that same way.
Once this is done I email it out in excel format to different managers that need to make commits and then email the spread sheet back to me then I import the spread sheets and record there comments.

If at all possible I would like all the files to import into one spread sheet just into different tabs. I have a bout 4 different group.

Below are some of the articles I’ve read about exporting from access to excel.

Looking for any advice example ect

http://www.databasejournal.com/features/msaccess/article.php/3563671/Export-Data-To-Excel.htm

http://www.vb123.com/toolshed/98docs/excelbe.htm

http://www.tek-tips.com/viewthread.cfm?qid=1201057

http://zmey.1977.ru/Access_To_Excel.htm

http://www.databaselessons.com/access-data-to-excel-2.php


Thanks for all your help and advice
TCB

If I have Oracle Database on the Server, and I want to import Table to MS Access with a condition, etc. the TABLE on Database have 4 Field, But I want to import for 2 Filed . How can I connect to the Database and Import 2 field to MS Access
Thank you
Wiwattana B.

Hi,

I have a student database in MS-Access, And I want to fetch some part of data from ms-access to excel.There is a macro in access where 20 to 25 queries in that macro, I want to hit macro first so macro will do all the calculation in ms-access, then at end final query will store result data in "Student_Final_Result" table, After that I want to fetch all data from "Student_Final_Result" to excel in Sheet("Student").range("a1"), But I am not sure how will I do this.....

Database Name Student_Data.mdb
Macro_Name Stu_Calc
Temprary_Table Student_Final_Result
Excel_Workbook Student Result
Result_Sheet Student
Paste_Range Sheet("Student").range("a1")

Please help me to resolve this problem.

Thanks & Regards,
Kashif.

Hello,

I currently have canned reports using Oracle Discoverer. It is a reporting tool that helps the end user pull data from Oracle and can export it to text, Excel, etc. I am trying to use the Oracle Discoverer canned reports to connect or import to a Microsoft Access database, so the Oracle data refreshes in the database automatically. I've been exporting the Oracle data in Excel and importing into Access, which sounds like a Newbie thing to do. Would anyone know how to connect Oracle Discoverer to MS Access? Thank you.

Hi,
I need some help to export the report from MS Access 2010 to MS Word 2010 format. Here are my questions:


a) I see RTF format not the word format during the export?
b) Is there any other way to export the formated report in correct format?
c) Can I fix formating like: 1" space from left etc. during the export?

Thanks,
Amir

HISTORY: I've been working on creating MS Excel charts from MS Access as the closing piece of more than 3K lines of code used to generate a single complex report. Unfortunately, I have not been able to figure out how to define the data range in my code. I can dynamically define data series and create the chart, but at the end of the method I find myself having to go into Excel, and manually defining the data range. Once this manual step is done the chart automatically adjusts to be exactly what I am looking for. Unfortunately the approach I have been working with to make the chart is not very elegant.

CHARTWIZARD EXCEL VBA METHOD: Recently I learned about the Excel VBA ChartWizard method and am considering re-writing code to support this approach, but have not found a working example of this code.

RANT: Despite contacting the author and publisher of Alison Balter's "Mastering Microsoft Access 2000 Development," (ISBN 0-672-31484-3) the only response I have received is "talk to someone else..." if you want any of the code, examples, and sample office automation databases from chapter 26 of our book (or the companion CD) to become functional.

OBJECTIVE: Graph multiple data series in an Excel chart.

REQUEST: Does anyone have a good sample code for creating an Excel chart from MS Access (with multiple data series) using calls to the MS Excel VBA chartwizard method?

COMMENT: I've looked at CraigDolphin's code and found it very specific to his application and am looking for a more rudimentary example.
________
Bmw r75/5

Purpose: Generate Completely formatted letter in Ms.Word from Ms.Access.

I Designed as screen with some options. Clicking on GENERATE button in Ms.Access Form displays a letter with the selected options in Word.

This Went on well, but the problem arose when I needed to input BULLET in the letter for some options.

Question: How could I insert a bullet in a running text in Word, with code from Ms.Access.

eg:
This is my first statement.
I was first in my class
Later I dropped to 5th after I thought to fall in love

We have WSS3. I am a site collection admin. The SharePoint server is at remote location at our vendor.
I can link from MS Access to all the cusom lists - lists created by users.
I can not link from MS Access to the admin lists, for example the Site Hierarchy list. This is https://mysite.com/_layouts/vsubwebs.aspx
I know this is a LIST. It has columns Site URL, Title, etc.
I need Read only access. How can I do it?

I am controlling an MS Excel spreadsheet from MS Access using VBA. I want to change a column from text format to Date format. To change the format is simple, but it does not activate the fields until they are double clicked on. When I try to programme this it does not work. I have tried PasteSpecial and TextToColumns, but neither are working.
Please can someone help?
Thanks

Hi everyone,

I am new here. I have a question and if any experts can provide me with some answer it's greatly appreciated.

I am using window 7 x64 MS Access2007, and I'd liketo know how to SHELL the Window Fax and Scan from MS Access with one button click event.

Thanks in advance.

P.S. I tried to use SHELL but I got
"File not found"

Phil

I would like to export text data from MS Access using VB code. The export file needs to go to the public desktop directory in Windows 7. There are some permission issues when I do this. Has anyone been able to work around this?

Thanks,
Greg


Not finding an answer? Try a Google search.