Connect to Oracle using VBA code

Question: I'm developing an application using Access 2003/XP/2000/97 as the front-end and Oracle as the back-end database. I've run into an scenario wherein I'd like to suppress the "ODBC Driver Connect" window prompt that asks for the username and password to connect to the Oracle database.
So I've created a form called "Login", but I need help writing the VBA code that would connect to Oracle. Can you help?
Answer: To connect to Oracle as you described above, you'll have to first create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

Link to an Oracle table using VBA code

Question: In Access 2003/XP/2000/97, I'd like to link to an Oracle table using VBA code. How can I do this?
Answer: The first thing you need to do is create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.
To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.

Call an Oracle stored procedure using VBA code

Question: I'm using Access 2003/XP/2000/97 as the front-end development tool and Oracle RDBMS as the back-end database. There are packages, procedures, and functions in the Oracle database that I'd like to call from Access. How do I execute or call an Oracle stored procedure from Access?

e.g. application_program_interface(member_id, provider_id, service_date)

Answer: To call an Oracle stored procedure, you'll have to create a query using VBA code.

Create a pass-through query to retrieve the nextval from an Oracle sequence with VBA code

Question: In Access 2003/XP/2000/97, I would like to create a pass-through query so that I can retrieve the NextVal from an Oracle sequence. I would like to then assign the NextVal to a field on an Access form.
How can I do this?
Answer: To assign the NextVal (from an Oracle sequence) to a field on a form, you'll have to create a pass-through query using VBA code.

Create a pass-through query to retrieve the nextval from an Oracle sequence

Question: I can get nextval from sequence in Oracle with this command:

select seq_name.nextval from dual;

Is there any way to get nextval from the Oracle sequence through Access 2003/XP/2000/97 when docmd.runsql("select seq_name.nextval from dual") doesn't work?
I need to insert nextval (the same value) into two tables.
Answer: To get the nextval from an Oracle sequence, you will need to use a pass-through query in Access.
To create a pass-through query, create a new query. Select Design View and click on the OK button.