By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 996 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Jet 4.0 to Access 2.0 help needed

P: n/a
O

I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.

Here's the scenario:

2 people have the same Access 2.0 databases running in the Access 2.0
application.

I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). conn.open() (Note: This will even happen if I
specify Mode=READ)

I close the database in VB. (don't lookup, don't update, don't do
anything.) conn.close() (note: works the same if I also dispose the
connection).

The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. In VB, I can't reopen the connection again. I can't even
open a stream into the file. Basically, no new file access to that
..ldb file once I close the connection.

Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? Or any other
solution? I've spent about a week on this one! I'm at the point of
rewriting the same code using VB6.

Thanks for any help.

-Owen
Aug 28 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On Aug 28, 12:14*pm, O <ow...@denofinequityx.comwrote:
I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.

Here's the scenario:

2 people have the same Access 2.0 databases running in the Access 2.0
application.

I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). *conn.open() *(Note: This will even happen if I
specify Mode=READ)

I close the database in VB. (don't lookup, don't update, don't do
anything.) *conn.close() *(note: works the same if I also dispose the
connection).

The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. *In VB, I can't reopen the connection again. *I can't even
open a stream into the file. *Basically, no new file access to that
.ldb file once I close the connection.

Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? *Or any other
solution? *I've spent about a week on this one! *I'm at the point of
rewriting the same code using VB6.

Thanks for any help.

-Owen
What is the code you're using to connect? What is the connection
string you're using?

As an aside, I wonder what would happen if you created an empty Access
2000 format database and used it to link to the tables in your Access
2.0 database and then used your .net app to connect to the Access 2000
database. In other words, let the Access 2000 database act as a
'wrapper' for the Access 2.0 database. This might allow Access to
handle the locking and translation and the Jet 4.0 OLEDB might handle
the 2000 format better. Might be worth a try.

Bruce
Aug 29 '08 #2

P: n/a
O
In article
<27**********************************@j22g2000hsf. googlegroups.com>,
Bruce <de***************@gmail.comwrote:
On Aug 28, 12:14*pm, O <ow...@denofinequityx.comwrote:
I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.

Here's the scenario:

2 people have the same Access 2.0 databases running in the Access 2.0
application.

I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). *conn.open() *(Note: This will even happen if I
specify Mode=READ)

I close the database in VB. (don't lookup, don't update, don't do
anything.) *conn.close() *(note: works the same if I also dispose the
connection).

The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. *In VB, I can't reopen the connection again. *I can't even
open a stream into the file. *Basically, no new file access to that
.ldb file once I close the connection.

Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? *Or any other
solution? *I've spent about a week on this one! *I'm at the point of
rewriting the same code using VB6.

Thanks for any help.

-Owen

What is the code you're using to connect? What is the connection
string you're using?
Here's the code - it's in VB2005:

dim conn as OleDBConnection

conn = New OleDBConnection("provider=Microsoft.Jet.OLEDB.4.0; Data
Source=p:\public\tax\taxdata.mdb;JET OLEDB:Engine Type=2")

conn.Open()
conn.Close()

The failure happens on close. (The close also happens on program
termination, I've tried leaving it open.) OLEDB issues a Delete to the
..ldb file, even though there are two other Microsoft Access 2.0 users
with the file open. It's like OLEDB doesn't see the other users, even
though they're clearly listed in the .ldb file.
>
As an aside, I wonder what would happen if you created an empty Access
2000 format database and used it to link to the tables in your Access
2.0 database and then used your .net app to connect to the Access 2000
database. In other words, let the Access 2000 database act as a
'wrapper' for the Access 2.0 database. This might allow Access to
handle the locking and translation and the Jet 4.0 OLEDB might handle
the 2000 format better. Might be worth a try.
You know, I was just thinking of a solution like this. I'll check it
out.

-Owen
Aug 29 '08 #3

P: n/a
O <ow***@denofinequityx.comwrote in
news:28**********************@denofinequityx.com:
I need to do some lookups and updates on some legacy Access 2.0
files (they've from another vendor and I'm not in a position to
update them). I was successful using VB6, but I've moved on to VS
2005 and ADO and having some serious problems.
Why in the world are you using ADO? Why not use Jet's native
interface, DAO?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 29 '08 #4

P: n/a
O
In article <Xn**********************************@64.209.0.89> , David W.
Fenton <XX*******@dfenton.com.invalidwrote:
O <ow***@denofinequityx.comwrote in
news:28**********************@denofinequityx.com:
I need to do some lookups and updates on some legacy Access 2.0
files (they've from another vendor and I'm not in a position to
update them). I was successful using VB6, but I've moved on to VS
2005 and ADO and having some serious problems.

Why in the world are you using ADO? Why not use Jet's native
interface, DAO?
Am I mistaken in that DAO is no longer available in VS 2005 and VB.NET?

-Owen
Aug 30 '08 #5

P: n/a
O <ow***@denofinequityx.comwrote in
news:30**********************@denofinequityx.com:
In article <Xn**********************************@64.209.0.89> ,
David W. Fenton <XX*******@dfenton.com.invalidwrote:
>O <ow***@denofinequityx.comwrote in
news:28**********************@denofinequityx.co m:
I need to do some lookups and updates on some legacy Access 2.0
files (they've from another vendor and I'm not in a position to
update them). I was successful using VB6, but I've moved on to
VS 2005 and ADO and having some serious problems.

Why in the world are you using ADO? Why not use Jet's native
interface, DAO?

Am I mistaken in that DAO is no longer available in VS 2005 and
VB.NET?
DAO is installed on every version of Windows that I know about. It's
a COM component, so if you can use COM components in those
development environments, you can use DAO.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 30 '08 #6

P: n/a
On Aug 29, 11:29*am, O <ow...@denofinequityx.comwrote:
In article
<27481030-3d87-4d63-a781-a4f227c77...@j22g2000hsf.googlegroups.com>,

Bruce <deluxeinformat...@gmail.comwrote:
On Aug 28, 12:14*pm, O <ow...@denofinequityx.comwrote:
I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.
Here's the scenario:
2 people have the same Access 2.0 databases running in the Access 2.0
application.
I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). *conn.open() *(Note: This will even happen if I
specify Mode=READ)
I close the database in VB. (don't lookup, don't update, don't do
anything.) *conn.close() *(note: works the same if I also disposethe
connection).
The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. *In VB, I can't reopen the connection again. *I can't even
open a stream into the file. *Basically, no new file access to that
.ldb file once I close the connection.
Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? *Or any other
solution? *I've spent about a week on this one! *I'm at the pointof
rewriting the same code using VB6.
Thanks for any help.
-Owen
What is the code you're using to connect? *What is the connection
string you're using?

Here's the code - it's in VB2005:

dim conn as OleDBConnection

conn = New OleDBConnection("provider=Microsoft.Jet.OLEDB.4.0; Data
Source=p:\public\tax\taxdata.mdb;JET OLEDB:Engine Type=2")

conn.Open()
conn.Close()

The failure happens on close. *(The close also happens on program
termination, I've tried leaving it open.) OLEDB issues a Delete to the
.ldb file, even though there are two other Microsoft Access 2.0 users
with the file open. *It's like OLEDB doesn't see the other users, even
though they're clearly listed in the .ldb file. *
As an aside, I wonder what would happen if you created an empty Access
2000 format database and used it to link to the tables in your Access
2.0 database and then used your .net app to connect to the Access 2000
database. *In other words, let the Access 2000 database act as a
'wrapper' for the Access 2.0 database. *This might allow Access to
handle the locking and translation and the Jet 4.0 OLEDB might handle
the 2000 format better. *Might be worth a try.

You know, I was just thinking of a solution like this. *I'll check it
out.

-Owen
I think you need to use Engine Type = 3 for Jet 2.0 (which is what
shipped with Access 2.0). If I recall correctly, type 1 = Jet 1.0,
type 2 = Jet 1.1, etc. This might be worth a try as well.

Bruce
Sep 2 '08 #7

P: n/a
O
In article
<5d**********************************@p25g2000hsf. googlegroups.com>,
Bruce <de***************@gmail.comwrote:
On Aug 29, 11:29*am, O <ow...@denofinequityx.comwrote:
In article
<27481030-3d87-4d63-a781-a4f227c77...@j22g2000hsf.googlegroups.com>,

Bruce <deluxeinformat...@gmail.comwrote:
On Aug 28, 12:14*pm, O <ow...@denofinequityx.comwrote:
I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.
Here's the scenario:
2 people have the same Access 2.0 databases running in the Access 2.0
application.
I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). *conn.open() *(Note: This will even happen if I
specify Mode=READ)
I close the database in VB. (don't lookup, don't update, don't do
anything.) *conn.close() *(note: works the same if I also dispose the
connection).
The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. *In VB, I can't reopen the connection again. *I can't even
open a stream into the file. *Basically, no new file access to that
.ldb file once I close the connection.
Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? *Or any other
solution? *I've spent about a week on this one! *I'm at the point of
rewriting the same code using VB6.
Thanks for any help.
-Owen
What is the code you're using to connect? *What is the connection
string you're using?
Here's the code - it's in VB2005:

dim conn as OleDBConnection

conn = New OleDBConnection("provider=Microsoft.Jet.OLEDB.4.0; Data
Source=p:\public\tax\taxdata.mdb;JET OLEDB:Engine Type=2")

conn.Open()
conn.Close()

The failure happens on close. *(The close also happens on program
termination, I've tried leaving it open.) OLEDB issues a Delete to the
.ldb file, even though there are two other Microsoft Access 2.0 users
with the file open. *It's like OLEDB doesn't see the other users, even
though they're clearly listed in the .ldb file. *
As an aside, I wonder what would happen if you created an empty Access
2000 format database and used it to link to the tables in your Access
2.0 database and then used your .net app to connect to the Access 2000
database. *In other words, let the Access 2000 database act as a
'wrapper' for the Access 2.0 database. *This might allow Access to
handle the locking and translation and the Jet 4.0 OLEDB might handle
the 2000 format better. *Might be worth a try.
You know, I was just thinking of a solution like this. *I'll check it
out.

-Owen

I think you need to use Engine Type = 3 for Jet 2.0 (which is what
shipped with Access 2.0). If I recall correctly, type 1 = Jet 1.0,
type 2 = Jet 1.1, etc. This might be worth a try as well.
I tried all the engine types. From what I can see, the main difference
is that engine types 3 will cause JET to rewrite the .ldb file as
double byte.

What happens, and my immediate problem, regardless of Engine type, is
that when the connection is closed, or the application is shut down
without closing, JET sends a delete message to the .ldb file, ignoring
that there are Access 2.0 clients connected to it. The file doesn't
get deleted, because the other clients are holding it open, but there's
a "DELETE PENDING" on the file, which prevents new clients, of any
type, from opening it, until all clients close it. Then it gets
deleted and you can restart from anywhere.

-Owen
Sep 2 '08 #8

P: n/a
On Sep 2, 11:23*am, O <ow...@denofinequityx.comwrote:
In article
<5dbab07d-23ef-49cc-8e4e-e9d41202d...@p25g2000hsf.googlegroups.com>,

Bruce <deluxeinformat...@gmail.comwrote:
On Aug 29, 11:29*am, O <ow...@denofinequityx.comwrote:
In article
<27481030-3d87-4d63-a781-a4f227c77...@j22g2000hsf.googlegroups.com>,
Bruce <deluxeinformat...@gmail.comwrote:
On Aug 28, 12:14*pm, O <ow...@denofinequityx.comwrote:
I need to do some lookups and updates on some legacy Access 2.0 files
(they've from another vendor and I'm not in a position to update them).
I was successful using VB6, but I've moved on to VS 2005 and ADO and
having some serious problems.
Here's the scenario:
2 people have the same Access 2.0 databases running in the Access2.0
application.
I open the database using JET 4.0 OLEDB and specifying Data Engine Type
1.0 (also tried 2.0). *conn.open() *(Note: This will even happen if I
specify Mode=READ)
I close the database in VB. (don't lookup, don't update, don't do
anything.) *conn.close() *(note: works the same if I also dispose the
connection).
The .ldb file is now locked up in what looks like a "DELETE PEND." The
people running the Access 2.0 application can still work, but they
can't quit and restart until everyone quits and the .ldb file gets
deleted. *In VB, I can't reopen the connection again. *I can't even
open a stream into the file. *Basically, no new file access to that
.ldb file once I close the connection.
Is there any other setup commands I need to use to properly
simultaneously use ADO and VB on legacy Access 2.0 files? *Or any other
solution? *I've spent about a week on this one! *I'm at the point of
rewriting the same code using VB6.
Thanks for any help.
-Owen
What is the code you're using to connect? *What is the connection
string you're using?
Here's the code - it's in VB2005:
dim conn as OleDBConnection
conn = New OleDBConnection("provider=Microsoft.Jet.OLEDB.4.0; Data
Source=p:\public\tax\taxdata.mdb;JET OLEDB:Engine Type=2")
conn.Open()
conn.Close()
The failure happens on close. *(The close also happens on program
termination, I've tried leaving it open.) OLEDB issues a Delete to the
.ldb file, even though there are two other Microsoft Access 2.0 users
with the file open. *It's like OLEDB doesn't see the other users, even
though they're clearly listed in the .ldb file. *
As an aside, I wonder what would happen if you created an empty Access
2000 format database and used it to link to the tables in your Access
2.0 database and then used your .net app to connect to the Access 2000
database. *In other words, let the Access 2000 database act as a
'wrapper' for the Access 2.0 database. *This might allow Access to
handle the locking and translation and the Jet 4.0 OLEDB might handle
the 2000 format better. *Might be worth a try.
You know, I was just thinking of a solution like this. *I'll check it
out.
-Owen
I think you need to use Engine Type = 3 for Jet 2.0 (which is what
shipped with Access 2.0). *If I recall correctly, type 1 = Jet 1.0,
type 2 = Jet 1.1, etc. *This might be worth a try as well.

I tried all the engine types. *From what I can see, the main difference
is that engine types 3 will cause JET to rewrite the .ldb file as
double byte.

What happens, and my immediate problem, regardless of Engine type, is
that when the connection is closed, *or the application is shut down
without closing, JET sends a delete message to the .ldb file, ignoring
that there are Access 2.0 clients connected to it. *The file doesn't
get deleted, because the other clients are holding it open, but there's
a "DELETE PENDING" on the file, which prevents new clients, of any
type, from opening it, until all clients close it. *Then it gets
deleted and you can restart from anywhere.

-Owen
I'm not sure what else to suggest here. David's idea posted earlier
to use DAO will probably be more efficient and may eliminate your lock
problems. If you do find a solution please let us know what you ended
up with.

Bruce
Sep 4 '08 #9

P: n/a
O
In article
<1b**********************************@i20g2000prf. googlegroups.com>,
Bruce <de***************@gmail.comwrote:

I tried all the engine types. *From what I can see, the main difference
is that engine types 3 will cause JET to rewrite the .ldb file as
double byte.

What happens, and my immediate problem, regardless of Engine type, is
that when the connection is closed, *or the application is shut down
without closing, JET sends a delete message to the .ldb file, ignoring
that there are Access 2.0 clients connected to it. *The file doesn't
get deleted, because the other clients are holding it open, but there's
a "DELETE PENDING" on the file, which prevents new clients, of any
type, from opening it, until all clients close it. *Then it gets
deleted and you can restart from anywhere.

-Owen

I'm not sure what else to suggest here. David's idea posted earlier
to use DAO will probably be more efficient and may eliminate your lock
problems. If you do find a solution please let us know what you ended
up with.
To me, it's obviously a bug in MS's latest JET OLEDB (and probably
OBDC) drivers.

Here's my current workaround:

Use VB6 to build DAO driven dll that talks to the Access 2.0 database.
Call the dll from VB.NET.

The likelihood of MS bothering to go back and fix a bug affecting early
Access JET OLEDB users is probably minimal, so the dll intermediary
workaround is going in.

-Owen
Sep 4 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.