ADO and Access... Argh!!

[texuspete00]

Man, there are just no good douments on this stuff anywhere. I'm going to have to go and buy a book. I've worked with some ADO code here at work, once the other guys got it started (since departed so I can't ask) with no troubles but it seems I can never get started though!!


look how stupid I am! I just have a form with a button to open and close the recordset... and I can't do it!

_______________________
Option Compare Database
Option Explicit

Dim CurConn As ADODB.Connection
Dim rstContacts As New ADODB.Recordset
Dim CurDB As Database

Public Sub cmdClose_Click()
rstContacts.Close
End Sub

Public Sub cmdOpen_Click()
Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " + CStr(CurDB.Name)
.Open
End With


Set rstContacts = New ADODB.Recordset
rstContacts.Open "Select * FROM Contacts", CurConn, adOpenDynamic, adLockOptimistic, adCmdText


End Sub
_________________________________-

Why am I so f'ing retarded? I get an error when trying to open this connection, not to the recordset yet, just the damn connection(CurConn.Open)....
Run-time error '-2147467259 (80004005)':
Could not use "; file already in use.

This is really annoying. I've gone through examples in VB6 where I can connect to SQL server and run ADO but I can never set the conection up on my own, for different apps. Books on the topic are annoying as well often not mentioning what references you should have hooked in. I've tried closing the connection to make sure it's not floating about with the immediate window... after all program bombs as soon as I hit open, so I can't hit close. Help!!

 

[PTNL]

based on the connection string, i'm guessing it's an access database. did you happen to have the access database open in access at the same time by any chance? if so, there's at least part of the problem. or was the code originally communicating with an access database, and the database reference was changed to "CurDB" ??

i'm also interested in the connection string, specifically the value for the database. you seem to be pointing to a locally declared database object, but i always thought that the "jet" connection was specific to just access databases.

also, does the "CurDB" need to be declared with the "new" keyword to instantiate a local instance of the database object?

as for the returned line of: Could not use "; file already in use. .... is that two-single ticks, or one double-quote mark? try putting a breakpoint at the line where CurConn executes the Open method (so that the code halts right before the Open fires). go into the command/immediate window and type "? CurConn", followed by a period, and check the values being stored in each property. something isn't being passed correctly.

also, is it vb6 or vb.net that you are trying?

 

[texuspete00]

I should have mentioned it's just visual basic for applications. But yeah, it's just the vb built-in to Access, so everything is as local as can be. But closing ACcess and running the code is impossible. I just don't want to fall back on DAO, as it is no longer going to be supported. If I was using that, I wouldn't be facing such troubles now. I'm going to read your post a few more times, and give it another go.

Oh, and curDB thing... yeah I put that in there when looking at examples. I think thats one part where the locality is helping. I think the only thing happenning there is the Set to "CurrentDB". But yeah I'm a bit confused as in other instances I set up just the connection and recordset. "CurDB.Name" does accurately translate into the path of the database in the connection string setting.

 

[guito13]

docmd.close

 

[texuspete00]

Alright... confused by what you wanted me to do... I just put that in the immediate window, and it closed access and vba but left there windows empty. Re openned the prog and access and it executed. I must have left something hanging open at some point. I had closed and launched the program manually before, maybe the reboot?. Ah well, I'm just trying to get a grasp on what happenned. That exact code is working.

I appreciate it guys. My DAO skills have been dinosaured.

 

[KRiTiKuL FX]

If I understand this correctly, you're trying to open the Access database that you currently have open. Make sense?

My question to you is, if you have a form, why do you need to use code to get a recordset? If you're wanting to update information on the form itself, just use the recordsource of the form itself.

 

[Hartlove]

There are lots of times when you're dealing with forms that have no recordsets associated with them; or with forms that can have more than one recordset, depending on what you want the user to be able to do on that particular screen.

 

[texuspete00]

Yeah, all I have here is basically the connect and close code. Yeah I setup a recordset but you can see I could have done anything there. The whole point is now I can do anything you normally could with programming. Loops, load values into arrays, create classes and objects. Sometimes I might over use code vrs built-in but all thats really here is those initial steps. Skies the limit now. Not really done anything here yet to declare I could have done it another way. I've got a few ideas... I just needed to get the thing going.