Doubts regarding "making SQL queries with VBA code"

[shandoo27]

Hi,

I have a few doubts regarding how to make SQL queries to link tables from MS access buttons using VBA coding.

Given: I have a list of link tables connecting Oracle and MS access with info stored in these tables.I also have a list of text boxes and drop down lists also to get information from the user . Now when the user clicks on search button.

Requirements:
I have to search for the the particular row in the table given these input values for the primary key ( or composite key ) and then after retrieving the row from the table I have to print these values on to a set of text boxes in the form .

Please guide me as to how to go about this problem.

I would be thankful if you could also give me a sample code to understand

Thanks in advance for the help ,

 

[llkhoutx]

Merely query the data with the appropriate primary key.

Be very cautious in joining Oracle and Access tables. If you do, all Oracle tables must be imported by Jet to join the Access table. The Oracle table(s) may be bigger than the 2G mdb (Access) size limitation and your program will hang, forever.

 

[shandoo27]

One of my doubts is once u write the query, where will the queried output values be stored. Can i define variables and then assign them to it ? Because i want to print the row in the corresponding text boxes .

Thanks in advance for the help.

 

[LPurvis]

Access (or rather Jet/ACE) doesn't import the server table rows per se. But it will read them into memory as best it can (paging out as required) though for a join on the PK there's every chance that only the PK index would be read fully - probably much, much smaller than the full table itself. (Which is why simple ODBC linked table requests that could appear to be risking horrendous overhead can sometimes still perform very reasonably).

None the less, such heterogeneous joins are still something you want to avoid. (As llkhoutx mentions).

Just to expand on some points...

>> where will the queried output values be stored
What do you ultimately mean by this? Are you saving the results of the query in some way? (As they won't be stored anywhere otherwise).

>> Can i define variables and then assign them to it
It depends upon what you're doing. Jet SQL itself doesn't support variables, but like so much of Access work, it's VBA supplementing it which gives you flexability.
Rather than joining on tables you can alter the query definition or pass parameters for single valued expressions to query effectively and precisely.

Cheers.

 

[shandoo27]

Hi,

Yes , I wan't to save the results got from the query as I want to print them in the corresponding textboxes in the form . Is there any way by which I can achieve it .

One general Doubt: Suppose I did the SQl select operation , where would the output be shown ??

Regarding Oracle : Since my database here is Oracle and not MS access , are the link tables view only ones or if i modify the link tables it will be effected in the oracle database ?

Suppose I want to add a new row to the table in Oracle database , how do i achieve it with a VBA code in MS access 2007 ?

Thanks in advance for the help provided .

 

[DCrake]

Suppose I want to add a new row to the table in Oracle database , how do i achieve it with a VBA code in MS access 2007 ?

This can be very dangerous. Beware or be aware of the consequences. Adding data to a proprietory database such as Oracle can have dire effects.

You may think you are just adding another records to a table. But the native software does not know you have done so, and as such any inbuilt triggers etc will not take place. And in the extreme circumstances may cause corruption in your linked tables.

Lets imagine you have your own Access database backend that is full of relationships and cascading inserts and deletes. Or VBA code that runs once you add a record to a known table. Then someone else creates a totally new database project and links to some of your tables and starts to add/edit or delete your table data. It could lead to al sorts of issues, don't you think.

 

[LPurvis]

If the database you're hitting is indeed created for proprietary software then treat it very carefully indeed. There is absolutely a case to be made for not adding rows to it (or editing).
That's if it is such a database of course, and not one of your own (or your company's) creation. If so then, obviously, updates to server based databases are very common part of Access application work.
I wouldn't want you to feel too much trepidation about that as a process in general.

With that said, your linked tables will be inherently updatable in a very similar fashion to your Access tables (linked or not) if the target table has a primary key that was correctly identified upon creation of the linked table (either by Access with or without prompt or in code used to create the linked table).

There's no requirement to store or save your queried data anywhere if you plan to display it in a form. If you have some connection or timing concerns, you could persist it in a local table if you wanted - writing back to the server when desired - but, generally, the form bound to the query provides results that can be updatable and reasonably efficient.

Cheers.

 

[llkhoutx]

Take a look at this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=193043&highlight=unbound+form

 

[shandoo27]

I now understand the difficulties in the above situation. But then it would be kind of you to also tell how to avoid such errors and how to correctly create a new row in the oracle database .

Thanks in advance for the help provided .