not in list problem

[Dirk Goldgar]

ok now were talking although i don't quite understnad all of it, so
here are parts of the code:
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AppData\AppData.MDB;"

the above code is part of what it runs when the application opens.

the following code is what i use to open the tables

Dim rst As ADODB.Recordset

On Error GoTo HandleErrors

Set rst = New ADODB.Recordset
rst.Open "Select * from tblLocations WHERE ID = " & Me.ID, cnn, &
adOpenKeyset, adLockPessimistic


in response to your second point all the tables in the application
mdb are linked to the data mdb. The tables are not the recordsources
for the forms, they are the recordsources for the combo boxes though.

and yes all the forms are unbound.

how can i use a global connection object.

You *are* using a global Connection object. You said "connection
string" before, which is not the same thing at all, but I see from the
code you posted above that you are actually creating and opening a
Connection object. So that concern is dealt with, leaving us with the
fact that you and Access aren't using the same connection to the
back-end database.

Did you try "DBEngine.Idle dbRefreshCache" yet? Any effect?

You didn't answer my last question: why? Why do it this elaborate and
cumbersome way? Why don't you want to work with the linked tables?
Answers to these questions would really help me to advise you,
especially since we're moving into an area where I have little
knowledge, just manuals and help files to go on. I know DAO pretty
well, ADO only slightly. If your back-end database is an MDB file, why
use ADO at all? DAO would be more efficient. Are you using ADO in
anticipation of shifting the back-end to SQL Server or some other
client-server database? Jet and DAO do a pretty good job at handling
linked ODBC tables.

I'm signing off for the night. Maybe more information will make better
sense of the whole thing, in the morning.

 

[Brendan Reynolds]

Access is already maintaining a global connection for you, which you can
access using CurrentProject.Connection. As Dirk has said, the fact that you
are using one global connection while Access is using a different one may
well be the cause of your problem. My advice would be to dispense with your
global connection and use the one that Access provides. I can't see that you
gain anything from having two connections instead of one. At least give it a
shot and see if it does in fact solve the problem. Just replace the
reference to 'cnn' in your code with 'CurrentProject.Connection' and see
what happens.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

Well, without seeing the code that was not working, I can not say why it
didn't work! All I can say is that this method does work, and is the
usual and standard way of doing these things in Access. I'm afraid it sounds
like you read the wrong book. While I can understand your reluctance to
change tack now, there is an old saying that when you find yourself in a
deep hole, the first thing to do is to stop digging.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[mariobro]

ok, thank you for your advice i'll give it a try to your suggestion and see
what happens.

 

[Dirk Goldgar]

sorry for the confusion but i'm new on this stuff i've alway use
access just as a reporting front end, this is the first time that i'm
developing a complete application with it. I trie the dbengine.idle
statement and didn't work. The reason that i'm using unbound forms is
because before i started the application i started reading a book
about programming in access (specially creating multiuser
applications, which is what i need), one of the advices that the
authors give on the book is working with unbound forms, so i start
playing with them and at the beginig i like them (mainly because of
the freedom that they gave on using a single form to edit or add data
to the table without the need of specifying in which mode to open the
form, or in the case of edit mode always need to deal with the 1st
record of the table) but as i was working it started to get a litle
more complicated that i excpected (for example the sql commands to
open the recordset did'nt work properly that's why i'm dealing with
the connection object, at least that was the only solution that i
could come up with), still i would like to keep the application as it
is since the users started to use it and they like the way it's
currently working. they just ask me to add the functionality of be
able to add records to an lookup table which is where i'm stuck on.
Also one of the other recommendation on the book was to move away
from DAO and start using ADO.

Actually, I don't agree with the author's advice to use unbound forms,
though it's certainly an approach that I understand -- more so when
dealing with a client/server data store like SQL Server. But in
general, my policy is to let Access do what it was designed to do
wherever possible. There are many techniques for working with multiuser
databases, even using bound forms. And I certainly don't agree with the
author's recommendation to move from DAO to ADO, if you're working with
MDB files. Even Microsoft has backed away from that recommendation.

Please correct me if I'm wrong, and I really don't mean to give offense,
but it sounds to me as though you have programmed quite a bit of code
that you don't fully understand, and now you're being bitten by issues
stemming from the complexity of that code. It's not really possible for
me to debug the code without seeing it, and there's too much of it for
you to post it all, even if you wanted to. All I can say from the
information available is that I believe that you need to update your
lookup tables using the same connection that Access is using, if you
want your updates to be immediately visible in combo and list boxes. So
if your combo box uses a rowsource that pulls data from a linked table,
use that linked table to update the table, and use the CurrentDb.Execute
(DAO) or CurrentProject.Connection.Execute (ADO) method to do it.

 

[SFR]

Sorry, I'm a newbie and don;t know how to create a new thread but it looks
like there's some good expertise out there so here i go ...

If you look at Access 2003's help function and type "temporarily open a form
as a dialog box", you'll see at the bottom it mentions setting the form's
VISIBLE property to "No". I had a macro which did this but after installing
Access 2003, the macro no longer works - the reason I believe is that the
form no longer has a property called "visible".

I don't know anything about VBA which is why i stuck with a basic macro for
this use - is anyone able to offer advice and/or a solution?

Thanks,
SFR

 

[Dirk Goldgar]

Sorry, I'm a newbie and don;t know how to create a new thread but it
looks like there's some good expertise out there so here i go ...

It's a bad idea to piggyback on old threads, not least because your
message is likely to be viewed way down at the bottom of a list sorted
by thread and date. I don't use the web newsreader, but it really
shouldn't be hard to create a new thread.

If you look at Access 2003's help function and type "temporarily open
a form as a dialog box", you'll see at the bottom it mentions setting
the form's VISIBLE property to "No". I had a macro which did this
but after installing Access 2003, the macro no longer works - the
reason I believe is that the form no longer has a property called
"visible".

I don't know anything about VBA which is why i stuck with a basic
macro for this use - is anyone able to offer advice and/or a solution?

I don't have Access 2003 installed, but I think I would have heard if
the Form object no longer has a Visible property. Please give the
details of your macro, and explain exactly what happens when you run it.
"No longer works" isn't very informative.