Setting ADO connection to CurrentDB like in DAO 5

[DougP]

I used this DAO syntax for years

Dim dbs as Database
Set dbs = CurrentDB
---------------------------
Now for the new ADODB

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Zorro1265]

What takes the place of
Dim rst as recordset?

Whats a good book on ADO? Zorro

 

[DougP]

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection ' <<<<Note same as CurrentDb
Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
SQLCode = &quot;SELECT * FROM [STOCK CODE LOOKUP] WHERE STOCK_CODE = '&quot; & Me![Part Number] & &quot;';&quot;
Rs1.Open SQLCode, cnn, adOpenStatic, adLockOptimistic

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[MichaelRed]

The following:

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim rst As ADODB.Recordset

    strSQL = "Select tblDailyQuo." & AvgT & " "
    strSQL = strSQL & "From tblDailyQuo "
    strSQL = strSQL & "Where (((tblDailyQuo.tick = " & Chr(34) & StkSym & Chr(34)
    strSQL = strSQL & " ) and (("
    strSQL = strSQL & "tblDailyQuo.dtQuo) "
    strSQL = strSQL & "Between " & Chr(35) & StDt & Chr(35)
    strSQL = strSQL & " and " & Chr(35) & EndDt & Chr(35)
    strSQL = strSQL & "));"
    
    rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic

Produces an error:

Run time error 91

Object Variable or With Block Variable Not Set

The strSql can be generated in the immediate window with:

? strSql

The String returned can be copied from the immediate window and pasted into an 'empty' query SQL View, executed and returns the Expected results.

At A total loss as to why this is a problem. The Cnn properties, viewed in the immediate window all appear to be what is expected. Any / All attempts to view or manipulate the rst properties fail - mostly with the same error. Even the 'cheat' book references say this SHOULD work - they just set some of the properties seperatly from the open function.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[pflangan]

before you open the recordset you have to create a new recordset object using

Set rs=new ADODB.recordset
rs.open &quot;SELECT * FROM Table&quot;, Currentproject.connection

rs.close
set rs=nothing


btw. don't bother wasting your time creating the adodb.connection reference, just use the currentproject.connection as i have above

tada!

 

[DougP]

Sorry about that I forgot a line

Dim Conn2 As ADODB.Connection
Dim Rs2 As ADODB.Recordset
Dim SQLCode As String
Set Conn2 = CurrentProject.Connection
Set Rs2 = New ADODB.Recordset ' < I forgot this line
SQLCode = &quot;Select * From [TAP Field layout];&quot;
Rs2.Open SQLCode, Conn2, adOpenStatic, adLockReadOnly
' do other recordset stuff in here
Rs2.Close 'possible error see below
Set Rs2 = Nothing
Set Conn2 = Nothing

Note: also depending on what your SQL statement does you may get an error on
Rs2.Close

If you INSERT values or UPDATE values
it closes the recordset after its done and you get an error.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[DougP]

I like pflangan's a whole lot better
how 'bout a star !!! ;-)

Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset

SQLCode = &quot;Select * From yourTable Where YourField = &quot; & SomeValue
Rs1.Open SQLCode, CurrentProject.Connection '< note don't need Conn or any of that

Set Rs1 = Nothing
Set Conn2 = Nothing

;-) I *:->* :-V |-0 :-D :
The Whole gangs applauding you pflangan
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[MichaelRed]

Well,

I agree he rates a Star or two.

The doc I have notes that this is possible, but also notes that these recordsets will be noticeablly slower than those where a connection already exists. They (the Docs) also 'say' that this is NOT just because the connection needs to be made at the point where the rst is set - but that continued performance penalties occur throughout the use of the rst. I know some (many?) no longer consider 'performance' to be of any importance, but I still hold onto the belief and expectation that it is an important objective of programming. MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[vb6novice]

Hope you experts can help.

My first VB6 project. I want to create a global connection to an Access 2000 database (Doesn't that mean I can use that connection to open recordsets in any code in the project?)

This and other threads on setting up a connection have been helpful. I've got code to establish a connection using a call to ConnectToDatabase function in my Sub Main procedure as follows:

connString = &quot;Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\MyFolder\MyDatabase.mdb&quot;

Public Function ConnectToDatabase(connString As String) As Boolean

Set gConnect = New ADODB.Connection
With gConnect
.ConnectionString = connString
.Open
End With

ConnectToDatabase = (gConnect.State = adStateOpen)

End Function

My problems/questions about this are:
How do I tell if I am successfully making a connection?
How do I open a recordset in code that is based on that connection?
Does the global connection affect connections made in a DataControl on a form?

I have tried the suggestions in this thread regarding using CurrentProject.Connection and I get Run Time error 424 'Object Required'.

All help is greatly appreciated.

 

[cmmrfrds]

This should be all you need on the state of the connection.
ConnectToDatabase = gConnect.State

For Access 2000 use the 4.0 provider.

My problems/questions about this are:
How do I tell if I am successfully making a connection?
State

How do I open a recordset in code that is based on that connection?
rs.open sqlString, gConnect, 3,3
OR
rs = gConnect.Execute sqlString

Does the global connection affect connections made in a DataControl on a form?
Not a good idea to open too many connections since there are a limited number available. Each connection is a separate instance. Go ahead and use the same connection for all the recordsets.

Here is the easiest way to make and test a connection string.

This is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multiple tabs.
6. use the microsoft Access provider – this will be Jet 4.0.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[dirkg]

thx MichaelRed for this interesting comment on performance. In the programs I make performance is top priority. I used to use DAO but now I have to transform everything to ADO since I upsized my database to Access Project and SQL Server. So any tips you have there for improving performance are very welcome...

Greetz,


Dirk

 

[MichaelRed]

Way many to long agos and far aways, I worked in controls systems. The particular object of my efforts was one of the &quot;Atom Smashers&quot; at a University research lab. The &quot;powers that were&quot; wanted (reather obviously) the maximum amount of flexibility in the set up and operation of the complex, and this 'dictated' the omission of a great number of what I considered 'reasomnablness' checks in parameters. On the other side, this same committe of &quot;powers that were&quot; also desired to minimize the operating expense (read salary) and thus intended to entrust the momentary operation of the complex to usually grad students. In the 'discussion' of these (somewhat conflicting) objectives, I stumbled on the somewhat obvious theorm that there is a simple trade off between 'ease' (read education and training necessary) of operation and flexibility of use.

Today, as a 'programmer', I see the same issue - for example with the selection of ADO vs DAO. ADO has, perhaps, some additional degree of flexability at the 'cost' of some additional knowledge requirements of those who implement it and certainly some additional loss of speed. On the other hand, DAO can -within limits- provide a simpler and faster interface for accesssing certain data objects.

To some degree, MS apears -to me- to continously searching for the single 'rosetta stone' of each process (e.g. Data Access). At the moment, 'we' are being urged to use ADO, and offered hints that DAO will (soon) become obsolete. I would wish that MS could get its' collective mentality that several or more approaches would be useful. At least one 'tricycle', one street bike (with optional training wheels) and one speed racer approach.

The choice -and its implications- would be readily available and perhaps come somewhat closer to achieving the &quot;goal&quot; of universal data access.

I would generally expect DAO to be somewhat more 'efficient' in access to structured data and ADO to mostly be slower in data operations. In ONE recent thread, there was even some discussion that DAO 3.5 was considerably faster than DAO 3.6 on a relatively significant database transform, and DAO 3.6 was REMARKABLY faster than the DAO (3.6) approach

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over