Linking SQL Server tables to List Box in a form

[Zukkster]

I'm trying to move the tables of an Access database over to SQL server because they've got too big to work with, but I want to keep my Access front end.

I've linked a list box in my Access form to a SQL Server table, but when I try to open the form it takes ages to open. I assume this is because it's populating the list box from the SQL Server.

I've tried creating a Pass Through Query, but it takes ages to run - it's pulling back a complete table, but it's only 48 rows of data.

Any ideas as to I can speed the form opening time up so it's less than 30 seconds?

 

[EstuardoSierra]

Hello Zukkster:

Can you tell us how are you filling the listbox?
I now you're not asking, but you should consider that move from access to sql is not as smooth as may think.
Fisrt: You will need to replace all " with ' when dealing with sql commands.
The (Magical) update queries from mds. no longer exist in sql (*.adp) if you keep your MS access front end.

SQL and Access form are not copatible, so this criteria in your Access query
Forms![frmAny]![tboxAny]
will be replaced with something like this

cnn.Execute "sprocAny " & Me.tboxAny

Back to your question, you must be doing something wrong with the table and flds definitions to make SQL to take that long to retrive a simple (less than 10,000) rows

Good Luck
Estuardo

 

[Zukkster]

I'm populating the list box by setting the "Row Source Type" property to Table/Query and then creating a pass through query to SQL.

Thanks for the advice regarding compatability. I don't use the update query much because it's a transcational database, but if you know of any good sources of information that give more info on comapatability I'd very much appreciate it.