Fastest Database access

Hi all,

I am currently using a DataEnvironment coupled with a DEConnection to access a MS Access database as follows:

'****************** START CODE*******************

If denvCMan.dconnCMate.State = 0 Then
denvCMan.dconnCMate.Open
End If

If grstData.State = 1 Then
grstData.Close
End If

Set grstData = Nothing

grstData.CursorLocation = adUseClient

grstData.Open "SELECT * FROM terminal WHERE TID = '" & sTID & "'", _
denvCMan.dconnCMate, adOpenForwardOnly, adLockReadOnly, adCmdText

grstData.ActiveConnection = Nothing

If grstData.State = 1 Then
grstData.Close
End If

If denvCMan.dconnCMate.State = 1 Then
denvCMan.dconnCMate.Close
End If

'****************** END CODE*********************

The problem is that the database is remote and the programme is running very slowly, especially as more users log on.

Which is the fastest way to access a database (it can be SQL, it doesn't have to be MS Access) via VB6? I will have about 5 users maximum but the data must be robust.

Thanks very much,
Nicola

Hi,

>I am currently using a DataEnvironment coupled with a DEConnection to access a MS Access database ...
>Which is the fastest way to access a database


Please drop it as soon as possible and just use ADODB objects ( Connection/Recordset/Command...) to connect to you database/fetch data/etc)

>I will have about 5 users maximum but the data must be robust

If you're talking about 5 simultaneuos users on Access you're very likely to be in trouble. If you have a chance to switch to MSDE do so.


Shurik.

Hi,

>I am currently using a DataEnvironment coupled with a DEConnection to access a MS Access database ...
>Which is the fastest way to access a database


Please drop it as soon as possible and just use ADODB objects ( Connection/Recordset/Command...) to connect to you database/fetch data/etc)

>I will have about 5 users maximum but the data must be robust

If you're talking about 5 simultaneuos users on Access you're very likely to be in trouble. If you have a chance to switch to MSDE do so.


Shurik.


Dear Shurik,

Thanks for the reply.

Would I need to use a control to reference ADODB objects and, if so, which one?

Also when you advise me to switch to MSDE is this the same as ADODB objects?

Thanks once again,
Nicola

>Would I need to use a control to reference ADODB objects and, if so, which one?

You need to reference the ADODB library (go Project/References/Microsoft ActiveX Data Object 2...)
Don't use ADODC control at all (if it's what you meant). Please have a look at the tutorial and some other info on ADO

http://www.visualbasicforum.com/showthread.php?t=39722

>Also when you advise me to switch to MSDE is this the same as ADODB objects?

No not really, MSDE is database engine (a scaled down version of the MS SQL server)
and can be downloaded for free from the Microsoft site. (but be suer to check the licence condition if you're going to distribute your application)


Shurik.

Thanks very much for all your help.
Nicola

Cheers

Cheers

Hi again,

I presume that I don't use any control at all I just reference as per the tutorial:

Dim iID As Integer
Dim sManuf As String
Dim sName As String
Dim sRefNum As String
Dim sDesc As String


Dim myConn As ADODB.Connection
Dim myRecSet As ADODB.Recordset

Set myConn = New ADODB.Connection
myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\CertManagement\DataBase\Test\CertManagement.mdb;Persist Security Info=False;Jet OLEDB:Database Password=csc;Jet OLEDB:New Database Password=csc"
myConn.Open

Set myRecSet = myConn.Execute("SELECT ID, manuf, name, refNum, desc FROM product")

myConn.Close

iID = myRecSet.Fields.Item("ID").Value
sManuf = myRecSet.Fields.Item("manuf").Value
sName = myRecSet.Fields.Item("name").Value
sRefNum = myRecSet.Fields.Item("refNum").Value
sDesc = myRecSet.Fields.Item("desc").Value


What ODBC setup - do I need to create a new data source name (DSN) in conjunction with the above or can I just use the code on it's own?

Thanks
Nicola

Hi,

You don't need ODBC (why do you think you do by the way?), the above code should work just fine.

Hi,

You don't need ODBC (why do you think you do by the way?), the above code should work just fine.

Thanks,

I was looking at a VB book in the ADO section and ODBC was mentioned.

There seems to be a problem though, when I run the code I get the following run time error:

Run-time error '3265':

Item can not be found in the collection corresponding to the requested name or ordinal

Any ideas?

Thanks,
Nicola

Try to modify your SQL string to:


SELECT [ID], manuf, [name], refNum, [desc] FROM product

Try to modify your SQL string to:


SELECT [ID], manuf, [name], refNum, [desc] FROM product


I tried that - still same error message (what do the square brackets mean?)

I tried that - still same error message (what do the square brackets mean?)

bracket is being use if you use a reserve words as fieldname i.e. "name", "desc" and it is also use if you have a field name that has a space in between i.e. Item Description

the above mentioned fieldnames should be enclosed with brackets [name], [desc],[Item Description] but avoid using them and you might as well look in ...

and for your error
myConn.Close => this should be placed after retrieving the value of your recordset


iID = myRecSet.Fields.Item("ID").Value
sManuf = myRecSet.Fields.Item("manuf").Value
sName = myRecSet.Fields.Item("name").Value
sRefNum = myRecSet.Fields.Item("refNum").Value
sDesc = myRecSet.Fields.Item("desc").Value

'it should be placed here
myRecSet.Close
myConn.Close


btw:
you should close the recordset first before closing the connection and set them to nothing after closing to release them from memory i.e.

set myRecSet = Nothing
set myConn = Nothing


and you should use the open method when using a select query execute method is best use if you are going to run an action query such as INSERT,UPDATE, and DELETE query. Execute method always return a forwardonly cursor which prevents you from moving backward (by using the MovePrevious method) and the returned recordset is a readonly so, if you are planning to update the recordset later the recordset won't allow you since it is being opened as readonly.

It works now! - thanks very much