X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Pass-through Rowsource Vba Syntax, Access 2016    
post Nov 7 2019, 08:35 AM

Posts: 15
Joined: 26-July 14

I had been using vba to construct SQL rowsource to form controls especially on listboxes. I find it very convenient and was able to play around switching different rowsources with just one control. But the problem is odbc linked tables are a bit slow.

Now I found that using stored procs in mysql server is faster than linked tables. Since I am using vba SQL to display data there no need to use saved queries thereby reducing the clutter.

My code for SQL rowsource goes something like:

Dim strName as string

strName = "select col1, col2 from table where value = """"";

controlName.rowsource = strName

My question is, it is possible to a vba SQL to call a mysql stored proc to be displayed in a listbox not using a stored passthrough query?

Stored proc on mysql database:

CREATE DEFINER=`root`@`localhost` PROCEDURE `q_search`(
IN `qname` VARCHAR(50)

select *
from tablename
where colName LIKE qCriteria;


Using heidisql I was able to display the result by CALL q_search('r%'); command.

I have this form where there is a textbox (txtSearch), listbox (lstSearch) and I want to pass the value from txtSearch to ('r%') by declaring 'r%' as string
and call the procedure to display directly into the listbox without utilizing a stored query.

Can anybody shed light on this on what should be the appropriate syntax in vba SQL to include the connection string?

Thanks in advance.
This post has been edited by JPeePh: Nov 7 2019, 08:40 AM
Go to the top of the page
post Nov 7 2019, 11:20 AM

UA Admin
Posts: 36,518
Joined: 20-June 02
From: Newcastle, WA

I think you'll want to do this with either one saved passthru query or with a temporary passthru query created in VBA.

BTW, "VBA" and "SQL" are really two different things.

VBA is the coding language for Access and other Office applications which is used to create functions and subs that manipulate the interface.

SQL is the query language for most relational database engines, such as MySQL, SQL Server, etc. That includes the ACE relational database engine that comes natively with Access.

One is used exclusively with the database engine. That's SQL. The other is used within the coding environment of the Access interface. That's VBA.

The confusion comes because you can create SQL statements from within VBA and then send those SQL statements to the database engine to execute.
For example, when you use VBA to change the SQL for the rowsource in a Listbox or Combobox, you create a properly defined SQL Statement and give it to the database engine to return records into that rowsource.

In the case of a saved passthru, for example, you can save the connection string in that passthru query. You can change the SQL in that saved passthru as needed, using VBA.

Here's an example, based on SQL Server. The process would be similar for MySQL.

Public Function RefreshPTSP(ByVal qdefName As String, ByVal strEXEC As String) As String

    On Error GoTo errHandler

    Set DB = CurrentDb
    Set qdef = DB.QueryDefs(qdefName)
    With qdef
        strSQL = strEXEC
        .SQL = strSQL
    End With
    RefreshPTSP = qdefName

    'Disables any enabled error handler in the current procedure.
    On Error Resume Next
    Set DB = Nothing
    Set qdef = Nothing

    Exit Function


       MsgBox err & ": " & err.Description
End Function

In this situation, I create the SQL string and send it into this function from another procedure, such as the After Update event of a combo box or list box when a value for the WHERE clause is selected.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Nov 7 2019, 11:41 AM

UtterAccess VIP
Posts: 8,010
Joined: 24-May 10
From: Downeast Maine

It may be necessary to add:

.ReturnsRecords = True

or for an action query:

.ReturnsRecords = False

I am not familiar with MySQL, but in SQL Server the SQL for an action query is the SQL you would use to run the stored procedure in SQL Server Management Studio (the interface for working directly with the database tables, etc.).
Go to the top of the page
post Nov 7 2019, 11:59 AM

UA Admin
Posts: 36,518
Joined: 20-June 02
From: Newcastle, WA

Good point. I tend to save that in the properties of the PT,

Attached File  PTProperties.png ( 15.32K )Number of downloads: 4

but it would be more flexible to do it as part of the VBA function like that.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Nov 7 2019, 08:55 PM

Posts: 15
Joined: 26-July 14

I never use QueryDefs because you can simply write an SQL statement in vba to populate a control.

I want to know if its possible not to use saved passthrough and just execute the SQL statement in vba that is passthrough unlike the regular select statements in vba
Go to the top of the page
post Nov 7 2019, 09:40 PM

UtterAccess VIP
Posts: 2,934
Joined: 12-April 07
From: Edmonton, Alberta Canada

Ok, we in a bit of a catch 22 here.

I can make some SQL on the fly – save having to build a whole bunch of queries. Great idea!!

I will now start to build store procedures for each of this combo boxes. Ouch!! – That really throws cold water on the first idea that supposed to save developer time here.

One of the best solutions for feeding combo boxes from SQL server is to simply create views. Save the view SQL side, and then link to that view. I do this so often, that I have a VBA routine that I simply “run” each time I need to link in the one new view.

You can use a pass-through query, or use a store proc but the access client CAN NOT FILTER these. I am going to repeat this again:

The Access client cannot filter a PT query, nor can the client filter a store proc. As a result, I would avoid using PT or store prod’s to fill combo boxes. There are some exceptions, such as a report prompt screen, but I that combo box is going to be bound on a form then DO NOT do this!

The reason is simple: The above rule applies (that Access can’t filter a PT query or store proc). So, if the combo box is say bound to a column, and has a value id of 1234.

If the combo box source is say 3000 rows? Well, the access client can’t filter that data, so it going to scan, pull and WORK on those 3000 rows until such time it finds a match.

If your combo box is even larger, then you going to notice some REAL slowdowns as Access struggles to scan the 3000 rows to get the CURRENT matching value. You will find if you have navigation to the next record, while the combo box is not being re-loaded, it is be re-scanned for a match. It will start to run VERY slow with a large list.

Now, pulling data via a PT query, or store proc is the FASTEST way to pull data into that combo box, but once filled, then Access can’t index the combo box!!

If you bind the combo box directly to a linked table, (or view) then when you move to a new record, Access does not actually pull the 3000 rows but MORE important does not SCAN the 3000 rows. It pulls the ONE row! It not until you crack open the comb box does the records get pulled.

So, the least amount of work for the best performance?

Create a view server side. Link to the view.

And DO NOT use SQL in the combo box query source, but ONLY put in the name of the view. (Of course if the view returns several columns, then of course the settings for the comb box must still be setup).

The above is the least amount of work, and in near all cases will perform better than a PT query, or a store proc. And the reason is as per above:
The access client can’t filter a PT query nor can it filter a store proc.

So, if that combo box is bound to a column in the form, then Access will struggle to scan and pull out the ONE row for display as you navigate in that form.

You can also of course place SQL in the combo box that has a source as a linked table (or view), but they tend to cause a noticeable delay. You can maybe get away with 2-3 of these on a form, but after that you have to resort to ONLY using the view.

So the “trick” here is ONLY place a view name or linked table as the row source for the combo box – no SQL in the row source.

And if all of the above is not fast enough, then you can consider loading the combo box data into a recordset, and it is legal to assign the recordset to the combo box rowsouce, but it really not wort the hassle.

Also you MUST place a index and sort column on the description column you are using (or use the NO-locks hint in your view). If you don’t do this, then you find stray “table blocking locks” appearing on the server – and this will prevent other users from pulling data from such tables.

So a PT query or store proc will fill that combo box really fast, but you then find out that the access client can’t filter the combo box to the one row without a time consuming scan of that data. I actually think the issue is that PT query, or a store proc can’t be indexed or searched correctly as a local dataset, but a linked table, or view can be.

And of course, by using views, then you don't deal with connection strings in code - so that is a bonus of the above approach.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st January 2020 - 12:56 PM