MDB size increasing !

[Naimesh Trivedi]

Hello ! I am trying to connect to access 2k from my vb
application and running updates thro' a process , which
parses one column of data in a table in to 5-7 diff.
column in the same table of around 6 Lac records, doing
this with simple update query in access adds around 40 MB
to size whereas doing so from vb application with query on
front end with ADO and Jet 4.0 driver reaches to 2GB
limitation and giving error.

This probl. is not coming when i select msdasql (Microsoft
sql driver) - odbc one, which is slow.

Pls guide me on this and also tell which one is good from
vb to access 2k and why ?

Thanks.

Regards

Naimesh Trivedi
(e-mail address removed)

 

[Dirk Goldgar]

Hello ! I am trying to connect to access 2k from my vb
application and running updates thro' a process , which
parses one column of data in a table in to 5-7 diff.
column in the same table of around 6 Lac records, doing
this with simple update query in access adds around 40 MB
to size whereas doing so from vb application with query on
front end with ADO and Jet 4.0 driver reaches to 2GB
limitation and giving error.

This probl. is not coming when i select msdasql (Microsoft
sql driver) - odbc one, which is slow.

Pls guide me on this and also tell which one is good from
vb to access 2k and why ?

Thanks.

Regards

Naimesh Trivedi

Any reason you don't just use DAO? Could you post the alternative code
methods that you have tried?

 

[Naimesh Trivedi]

Dear Dirk Goldgar,

Thanks for quick reply :

Code using Jet Ole which is causing size increase is :

Connection1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & txtDbPath.Text & ";Persist Security Info=False"
strDbPath = txtDbPath.Text

Changed code using Oledb for odbc - Microsoft access driver

strPath = "D:\scb\3Nov2003\ParseNumbers.Mdb"
Connection1.Open "Driver={Microsoft Access Driver
(*.mdb)};DBQ=" & strPath

And reason for using ADO (not DAO) is ADO is current
technology, faster and more options.

Regards,

Naimesh Trivedi

 

[Paul Overway]

Myth...DAO is not faster than ADO. DAO is the native database driver for
Jet database. ADO just adds another interface layer....there is nothing
faster about it.

 

[Brendan Reynolds \(MVP\)]

ADO is not current technology, either - current technology is now ADO.NET,
which is not a new version of ADO but a whole new ballgame. Classic ADO is
every bit as 'obsolete' as DAO.

ADO and ADO.NET are both useful technologies in appropriate circumstances.
But to avoid using DAO with a JET database from a VB app on the grounds of
'current technology' doesn't make a whole lot of sense. Naimesh doesn't say
which version of VB he's using, but I'm guessing it's VB6, as if it was
VB.NET he'd presumably be using ADO.NET. So, we have an 'obsolete' version
of VB (VB6) using an 'obsolete' data access technology ('classic' ADO) and a
database engine (JET) that's been in maintenance mode for years now. But we
can't use DAO because it's not 'current technology'!

 

[Dirk Goldgar]

Dear Dirk Goldgar,

Thanks for quick reply :

Code using Jet Ole which is causing size increase is :

Connection1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & txtDbPath.Text & ";Persist Security Info=False"
strDbPath = txtDbPath.Text

Changed code using Oledb for odbc - Microsoft access driver

strPath = "D:\scb\3Nov2003\ParseNumbers.Mdb"
Connection1.Open "Driver={Microsoft Access Driver
(*.mdb)};DBQ=" & strPath

And reason for using ADO (not DAO) is ADO is current
technology, faster and more options.

Regards,

Naimesh Trivedi

I think you should try using DAO instead, as it is optimized for Jet
databases. There's no knowing, though, whether this will solve your
bloating problem. We'd need to see the code that is actually performing
this operation.