Basic ADO help.

[CptCrusty1]

HI! I can write moderate code in DAO, I'm looking for basic translation for ADO so that I can "get the feel of it." Unfortunatley, I'm rather stupid thus I need to "see" examples to learn by.

ie.
[blue]
DIM rs as DAO.RECORDSET
DIM db as DAO.DATABASE
DIM strSQL AS STRING
DIM strVAR String

set db = currentdb()

strSQL = "some query I write looking for a single value"

Set rs = db.openrecordset(strSQL, dbOpenDynaset)

me.strVAR = rs!Value

End Sub
[/blue]

If anyone has a copple minutes, could you provide the same type of thing in ADO?

I really appreciate it.

Thx. CRUSTY

I live to work and I work to live.

 

[JerryKlmns]

Dim cnn As ADODB.Connection
Dim rs as ADODB.Recordset
'DIM db as DAO.DATABASE
'DIM rs as DAO.RECORDSET
DIM strSQL AS STRING
DIM strVAR As String

'set db = currentdb()
Set cnn = CurrentProject.Connection
strSQL = "some query I write looking for a single value"

'Set rs = db.openrecordset(strSQL, dbOpenDynaset)
Set rs = cnn.execute("strsql")
'me.strVAR = rs!Value
me.strVAR = rs.Fields(0).Value
rs.Close
Set rs = Nothing
Set cnn=nothing

End Sub

The returned Recordset object is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

 

[CptCrusty1]

Thanks! A couple questions tho.

SET cnn = CurrentProject.Connection
-- is this for connecting to another database?

I see my error on the me.strVAR = rs!Value. If my field is called USER_ID, then it would be..

me.strVAR = rs!USERID correct?

I live to work and I work to live.

 

[Golom]

> is this for connecting to another database?

No. It is a connection to the database opened by Access in the current session. If you want to open some other database then you will need to provide a connection string for that other database.

me.strVAR = rs!USERID correct?
You said your field is USER_ID so the reference should be to me.strVAR = rs!USER[COLOR=red yellow]_[/color]ID

 

[easyit]

CurrentProject.Connection = connection to current project...

But you could build a connection string to another database and set cnn tot that:

Dim cnn As ADODB.Connection
Dim ConnStr As String
On Error GoTo Error_Handler
'if using a secured db use the string MdwLocation to hold the path to the workgroupfile
'Here we use the default mdw
MdwLocation = "Jet OLEDB:System database=" & SysCmd(acSysCmdGetWorkgroupFile)

'the loginaccountname is db_admin
dim strRemoteDbName as string
strRemoteDbName = "N:\my_databases\mydatabase.mdb"
ConnStr = "data source=" & CurrentDb.name & ";" & MdwLocation & ";user id=db_admin;Password='MyDBPasword'"
Set cnn = New ADODB.Connection

With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = ConnStr
    .Open
End With

NB This is not a tested example; just trying to show you possibilities...

EasyIT

 

[JerryKlmns]

>SET cnn = CurrentProject.Connection
-- is this for connecting to another database?

Dim App_Cnn As ADODB.Connection

Set App_Cnn = New ADODB.Connection
With App_Cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source") = "c:\myDBase.mdb"
    .Properties("Jet OLEDB:System database") = "c:\myDBase.mdw" [green]'Skip this if no user-level security[/green]
    .Properties("User ID") = TheUserID  [green]'Skip this if no user-level security[/green]
    .Properties("Password") = TheUserPswrd  [green]'Skip this if no user-level security[/green]
    .Properties("Mode") = adModeShareDenyNone [green]'or something else [/green]
    .Properties("Jet OLEDB:Engine Type") = 5
    .Properties("Locale Identifier") = 1033 [green]'Thats my locale[/green]
    .Open
End With

App_Cnn.Close
Set App_Cnn = Nothing

>me.strVAR = rs!USERID correct?

Correct but prefere rs.Fields("USERID").Value
You could skip .Value , its the default property of the fields collection

 

[CptCrusty1]

Oh boy... I'm confused (easy to do)...

I've alwasy heard the ADO is better than DAO. It looks like ADO takes more writing than DAO. So how is it better?

I live to work and I work to live.

 

[easyit]

Wow some attention to the current project...

 

[JerryKlmns]

Of course

?CurrentProject.Connection.Properties.Count
96

and here

http://www.connectionstrings.com/

there are many connection strings to enjoy more databases....


Aren 't we haveing fun or not?!?

 

[abaldwin]

I have a few good reference books you should look into that are really good for learning ADO.

ADO Programming in Visual Basic 6.0 by Steven Holzner
ISBN 0-13-085857-9

and

Visual Basic 6 by Dan Rahmel
ISBN 0-07-882576-8

The second one is a basic vb6 reference book that is small enough to keep by your keyboard.

The first is a really good documentation and use of ADO. Good examples and very detailed. I would suggest these not only to learn but to keep as desktop reference material. We all use them.

I do not believe that you are Stupid as you stated in your original post, otherwise you would not be able to understand there is a difference.

Basically the connection object you asked about in his example is utilizing the current database connection. The connection object is the pointer that tells the ADO objects the location, type, and connection information for ANY data source you have drivers for. In other words you can connect to external Access, SQL, Pervasive, etc etc databases within your current access application.


If you have any further questions feel free to post.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[CptCrusty1]

WOW!! OK, EVERYONE THAT REPLIES GETS A STAR NAMED AFTER THEM......LOL... just don't ask me to point to it.... LOL

Ok...really, I can't believe a simple question got this much attention. This gives credit to Tek-tips members attentiveness and professionality. You wonder why I keep coming back here....

Thanks everyone. I'll try it in a while.....(GOTTA GET BACK TO WORK ON "IMPORTANT" STUFF).

Crusty.

I live to work and I work to live.

 

[cmmrfrds]

Check this link.

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnardao/html/daotoado.asp

 

[CptCrusty1]

ARRRRRRRGUH! I'm so confused. My db is called Referals.mdb and I'm not doing anything outside my db. SO:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset


set cnn=CurrentProject.connection
rst.open 'I want to open and add records too, a table'

rst!Field1 = txtBox.Test1
rst!field2 = txtBox.Test2

I keep getting an error "Run-Time Error 91: Object Variable or With block not set. at the "rst.open" line. There is more than that, but its not liking anything else....

Crusty.

I live to work and I work to live.

 

[PHV]

You may try something like this:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "NameOfTable", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886