Anyone who any good sites that will give tips on usinf VBA with Oracle. I know the basics, but want to do this right. ie best way to do searches, run reports etc. I assume it will be mainly SQL pass through. I would post on the Oracle boards, but there never seems to be that much activity. Nick (Everton Rool OK!)
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Oracle BE with Access Front end
- Thread starter nickjar2
- Start date
Hi Nick,
I don't know of any sites but I have done a few things like this in the past. Particularly using access to extract data or to use it as a reporting tool. If you have some specific problems post them on this thread and I might be able to help. Sandy
I don't know of any sites but I have done a few things like this in the past. Particularly using access to extract data or to use it as a reporting tool. If you have some specific problems post them on this thread and I might be able to help. Sandy
- Thread starter
- #3
Hi Sandy,
Cheers for replying. Basically, here r a few things that I will need to do.
Display an unbound form, with several subforms. Obviously unbond, othrwise network traffic, pc not coping etc.
Do various seraches on, say, surname. I assume i will create an index on the surname field in Oracle, then send a passthrough query based on the criteria.
If I wanted to run a report eg. all meters that have been maintained in April. Would i build an unbound report, and in code, specify an SQL query as the recordsource, and then bind the text boxes to the correct fields in code?
Another Q. Let's say I have 1 million customers. I need to browse the customer form. I open the form. My SQL will say 'SELECT * from CUSTOMERS'. Now I am assuming that using SQL Passthrough, this sql will get executed on the server. Obviously I wouldn't want these million recs coming across the network onto the client machine. If I then said goto the first record, and then bind the controls to the recordset, would only that FIRST record come onto the client??
If I have warbled, pls let me know. I am rushing here. Nick (Everton Rool OK!)
Cheers for replying. Basically, here r a few things that I will need to do.
Display an unbound form, with several subforms. Obviously unbond, othrwise network traffic, pc not coping etc.
Do various seraches on, say, surname. I assume i will create an index on the surname field in Oracle, then send a passthrough query based on the criteria.
If I wanted to run a report eg. all meters that have been maintained in April. Would i build an unbound report, and in code, specify an SQL query as the recordsource, and then bind the text boxes to the correct fields in code?
Another Q. Let's say I have 1 million customers. I need to browse the customer form. I open the form. My SQL will say 'SELECT * from CUSTOMERS'. Now I am assuming that using SQL Passthrough, this sql will get executed on the server. Obviously I wouldn't want these million recs coming across the network onto the client machine. If I then said goto the first record, and then bind the controls to the recordset, would only that FIRST record come onto the client??
If I have warbled, pls let me know. I am rushing here. Nick (Everton Rool OK!)
Nick:
To my knowledge, you're not going to be able to use Access reports unless you use linked ODBC tables with a DSN. Access reports & queries need this translation.
However, nothing says you can't also program an unbound form with ADO, even though the same tables are linked. You could also bind the form but with a SQL statement that selects just one record (based on a WHERE= & cboLookup type thing). You can use this same approach with a bound form to only seek one record (just don't set the recordset property until it's limited by a WHERE clause).
To my knowledge, you're not going to be able to use Access reports unless you use linked ODBC tables with a DSN. Access reports & queries need this translation.
However, nothing says you can't also program an unbound form with ADO, even though the same tables are linked. You could also bind the form but with a SQL statement that selects just one record (based on a WHERE= & cboLookup type thing). You can use this same approach with a bound form to only seek one record (just don't set the recordset property until it's limited by a WHERE clause).
Hi Nick,
I found the best way (in terms of speed of operation of reports and forms) was to use pass through queries. This meant that I could run the system WITHOUT linking the tables or directly importing the data into Access. Also it had the advantage that my Oracle trained colleagues could provide me with effiecient SQL to use on the more complex queries.
Some of the things I found useful were:
Use and unbound form to request the search criteria.
Don't create and delete QueryDefs but change the SQL only. This can save several seconds which is important when displaying single records on a form.
Return only the fields you require ie don't use SELECT * FROM. It is much more effiecient to let Oracle do all the work.
I found Subforms using linked tables or "fixed" pass through queries were not particularly fast (especially were there was a lot of data to display in the subform). In the end I used a sort of drill down technique to display limited amounts of data and the users were then able to get what they wanted.
This was a contact management system based on our main customer database, so there there was company wide data for read only purposes and local data (mainly added notes and contact phone numbers etc).
I have the code and so on archived so I'll look it out during the day. I'll post some examples Sandy
I found the best way (in terms of speed of operation of reports and forms) was to use pass through queries. This meant that I could run the system WITHOUT linking the tables or directly importing the data into Access. Also it had the advantage that my Oracle trained colleagues could provide me with effiecient SQL to use on the more complex queries.
Some of the things I found useful were:
Use and unbound form to request the search criteria.
Don't create and delete QueryDefs but change the SQL only. This can save several seconds which is important when displaying single records on a form.
Return only the fields you require ie don't use SELECT * FROM. It is much more effiecient to let Oracle do all the work.
I found Subforms using linked tables or "fixed" pass through queries were not particularly fast (especially were there was a lot of data to display in the subform). In the end I used a sort of drill down technique to display limited amounts of data and the users were then able to get what they wanted.
This was a contact management system based on our main customer database, so there there was company wide data for read only purposes and local data (mainly added notes and contact phone numbers etc).
I have the code and so on archived so I'll look it out during the day. I'll post some examples Sandy
Nick.
before you start, if you use this method then each user of the database will require their own copy of the front end Access. This can be a bit of an Admin problem when working in the early stages of bug fixes.
The code below will change the sql in a pass through query.
Function ModifySQL()
On Error GoTo Err_ModifySQL
Dim SQLString As String, MyDb As DAO.Database, Qdf As QueryDef
'Build SQL Here using values from form.
SQLString = "SELECT "
SQLString = SQLString & " FROM "
SQLString = SQLString & " WHERE Keyfield1 = Keyfield2 "
SQLString = SQLString & " AND Keyfield1 = Keyfield3"
SQLString = SQLString & " AND Field1 = " & data from form for selection
SQLString = SQLString & " AND "
SQLString = SQLString & " AND "
SQLString = SQLString & " AND "
SQLString = SQLString & " ORDER BY "
Set MyDb = CurrentDb
Set Qdf = MyDb.QueryDefs!qryPROCESSFLOW_SOURCE
Qdf.SQL = SQLString
Set Qdf = Nothing
Exit_ModifySQL:
Exit Function
Err_ModifySQL:
MsgBox "Error creating Query" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Accorde Reporting"
Resume Exit_ModifySQL
End Function
You can store the login and password to the database in the passthrough query. But do this with caution as people can then read it fairly easily. Sandy
before you start, if you use this method then each user of the database will require their own copy of the front end Access. This can be a bit of an Admin problem when working in the early stages of bug fixes.
The code below will change the sql in a pass through query.
Function ModifySQL()
On Error GoTo Err_ModifySQL
Dim SQLString As String, MyDb As DAO.Database, Qdf As QueryDef
'Build SQL Here using values from form.
SQLString = "SELECT "
SQLString = SQLString & " FROM "
SQLString = SQLString & " WHERE Keyfield1 = Keyfield2 "
SQLString = SQLString & " AND Keyfield1 = Keyfield3"
SQLString = SQLString & " AND Field1 = " & data from form for selection
SQLString = SQLString & " AND "
SQLString = SQLString & " AND "
SQLString = SQLString & " AND "
SQLString = SQLString & " ORDER BY "
Set MyDb = CurrentDb
Set Qdf = MyDb.QueryDefs!qryPROCESSFLOW_SOURCE
Qdf.SQL = SQLString
Set Qdf = Nothing
Exit_ModifySQL:
Exit Function
Err_ModifySQL:
MsgBox "Error creating Query" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Accorde Reporting"
Resume Exit_ModifySQL
End Function
You can store the login and password to the database in the passthrough query. But do this with caution as people can then read it fairly easily. Sandy
You can also have your Oracle guys create stored PL/SQL procedures to return reference cursors based on parameter input--this is the most efficient way to get a recordset in ADO (only ADO for this one). You'd also use PL/SQL procedures for your Updates, Inserts, & Deletes.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
