ListBox Refresh (Access 97 & 2002)

Topic

New Reply

Okay I shall try and explain this.
Our company wants everything to be written using ADO as they are migrating to Windows & OfficeXP.
So my issue is :

I open a form setting an unbound listbox using an SQL string.
I then allow the user to endit an item in separate text boxes.
Upon the Save of these changes I requery the list box. This listbox does not appear to update these changes.
This is a time delay beacuse if I check the table it is updated, and if I close and open the form it is updated.
Sometimes when I go to edit again, the updated information is loaded into the text boxes, from the listbox, even though the listbox
display does not show the updated information. I have tested using both ADO 2.1 & 2.6

Does anybody have any Idea?

Reply | Quote

Replies

Hi Cindy –
My goodness you are up late – I hope you are working at home. So your company is going to jump into ADO with both feet – and your life is going to get very interesting. In my view, and I think that of many others, ADO is still a work in progress. It works very well in some situations, and not so good in others. I have a couple of questions:

• Are you trying to do this in Access97 or in AccessXP/2002? I would only work in 2002 as it has the best set of ADO capabilities – ADO came out well after 97 and there is pretty limited support and quite a bit of flakey behavior.
• What is your back-end – Jet, MSDE or SQL Server? With Jet, there isn’t much advantage to ADO in my view – the real benefits come from using an ADO connection instead of an ODBC connection
• I presume you are still using a Jet-based (MDB) format rather than an ADP format, but if that’s not the case, there are lots of interesting wrinkles with ADP, and you can’t use DAO or ODBC connections.
  [/list]If none of this is useful, post the code you are using with the text boxes and the list box and maybe someone here can see what’s causing your grief. Cheers!

Reply | Quote

Not up late, am currently working in the UK.

We are using Access97 on our NT machines and Access2002 on those machines migrated to XP, hence the problem. Large company long migration period.
Backend currently Jet, but am trying to produce standards so all applications can migrate to SQLServer or Oracle and VB front end with ease, this is why ADO is the prefrence.

Currnet have a three second wait after my execute statement which irons out the problem, but 2 seconds is not long enough.
Does anyone have any ideas other than to drop ADO for Access development.

Reply | Quote

Pretty sneaky – I saw you were a Melbournite and jumped to a wrong conclusion. On the other hand you are working late in the UK now.

We use ADO exclusively in one addin Access MDB using a SQL Server backend and get sub-second response on complex forms that driven by multiple complex queries. (We are running a pretty robust server on a switched full-duplex LAN, which makes a significant difference.) We do however use ODBC linked tables in order to have bound forms. We use ADO in the addin so we don’t have to link to all of the tables and views in each database that uses the addin. The alternative to ODBC linked tables is to use unbound forms and write tons of code to make things happen – you can do that and not have any linked tables but things like combo boxes and subforms basically bite the dust. When you get to that point, you want to seriously consider the ADP approach. For the vast majority of applications we use ODBC linked tables because of the advantages in development cost that you get with bound forms. Simple unbound forms typically take 3 times as long to develop, complex ones with multiple nested subforms often take 10 times or more the development effort.

I hope this puts some of the issues in perspective – perhaps others will share their views.

Reply | Quote

Actually, comboboxes can work OK using callbacks or building a values list in code. It’s continuous subforms that aren’t possible with unbound forms.

Reply | Quote