Access query union "*" "(All)" to SQL Server view (1 Viewer)

supmktg

Registered User.
Local time
Today, 13:54
Joined
Mar 25, 2002
Messages
360
I have an Access application that is the front end to a SQL Server DB. The app includes a search form that has 5 combo boxes that filter the records. The combos are currently populated by Access union "ALL" queries that take a while to load. I'm trying to speed up the loading of the form by using SQL Server views instead of queries.

This query that works in Access:

SELECT tblVendor.VendID, tblVendor.VendName FROM tblVendor UNION Select '*' as VendID, '(All)' as VendName FROM tblVendor
ORDER BY tblVendor.VendName;

returns this SQL Server error:
sql server conversion failed when converting the varchar value '*' to data type int (#245)

VendID is an integer. How can I make this query into a view that works?

Thanks,
Sup
 

Minty

AWF VIP
Local time
Today, 18:54
Joined
Jul 26, 2013
Messages
10,355
In T-SQL the wildcard symbol is %
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:54
Joined
May 7, 2009
Messages
19,169
maybe use:

Union Select 0 As VendID, ...
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:54
Joined
Jan 20, 2009
Messages
12,849
I'm trying to speed up the loading of the form by using SQL Server views instead of queries.

That change probably won't make any significant difference to the speed.

This query that works in Access:

SELECT tblVendor.VendID, tblVendor.VendName FROM tblVendor UNION Select '*' as VendID, '(All)' as VendName FROM tblVendor
ORDER BY tblVendor.VendName;

returns this SQL Server error:
sql server conversion failed when converting the varchar value '*' to data type int (#245)

VendID is an integer. How can I make this query into a view that works

Code:
SELECT CONVERT(varchar(10), VendID) AS VendID, VendName
FROM tblVendor
UNION
SELECT '*' , '(All)'
FROM tblVendor
ORDER BY VendName;

The datatype of the subsequent subqueries must be compatible with those in the first.
Only the column names in the first subquery are used so no point including the others.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:54
Joined
Feb 19, 2013
Messages
16,553
with regards time to load, on the basis your list of vendors is a long list, it might be better to set a global ado disconnected recordset when your app opens (so the time is taken at that point), then assign this recordset to the combo recordset as and when required. If the list is very dynamic, might need to provide a refresh option (perhaps on a timer or button event).

Another alternative is to not load any records to the combo at all (except perhaps the *) until the user has entered at least one character. At which point the rowsource is updated by a filtered list - simplistically there are 26 chars so 1/26th the number of records to load
 

supmktg

Registered User.
Local time
Today, 13:54
Joined
Mar 25, 2002
Messages
360
Galaxiom, your solution did the trick, thank you very much! I may be optimistic, but by my estimation the form now loads much faster.
And, Thank you to all of you for your assistance!
Sup
 

Users who are viewing this thread

Top Bottom