adodb connection problem

[Bastien]

My app no longer updates the DB after I changed the location of the connections. I used to contain all the connection code in the updateBMO procedure. After i moved to the cmdStartBMOLoad sub, the app no longer updates.

What have I done wrong?



Private Sub cmdStartBMOLoad_Click()

Dim dbs As ADODB.Recordset
Dim dbs2 As ADODB.Recordset

Dim i As Integer
'open the oracle and access connections
'access
Set mcnn = New ADODB.Connection
mcnn.ConnectionString = "Provider=MSDASQL.1;" & _
"Data Source=MSRP13;Password=mellon;User ID=dc_dba;"
mcnn.Open
Set dbs = New ADODB.Recordset
dbs.CursorType = adOpenKeyset
dbs.LockType = adLockOptimistic
dbs.Source = sql


Public Sub updateBMO()
'Dim conn As New ADODB.Connection
'Dim rs As New ADODB.Recordset
Dim sql As String


sql = " insert into BMOList values(" & strId & ", '"
sql = sql & FName & "', '" & LName & "', '" & addr1 & "', '"
sql = sql & addr2 & "', '" & addr3 & "', '" & addr4 & "','" & city
sql = sql & "', '" & state & "','" & country & "', #" & DOB & "#, '" & var & "', '" & var4d & "')"

Set dbs.ActiveConnection = mcnn

dbs.Open sql, mcnn


End Sub

TIA Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[caseye]

Try

dbs.LockType = adLockPessimistic

 

[GHolden]

Your variables initialised in

Private Sub cmdStartBMOLoad_Click()

are private to that sub, so you need to make them global to use them in

Public Sub updateBMO()

 

[Bastien]

neither solution worked....

everything was fine when all the ADODB code resided in the sam SUB as the SQL statement. but that opens new connection on every insert and slows down the process.

I was trying to place it in the opening segments of the code to have it open once and close on ending the app. but somehow the code never seems to touch the DB.

any more ideas?

TiA Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[CajunCenturion]

You might want to try declaring the Connection Object and the Recordset option in the declarations sections of the form. This will allow them to be available to any routine wihtin the form.

Optiont Explicit
Dim ConnObj as ADODB.Connection
Dim RstObj as ADODB.RecordSet

Instantiate them in the Form Load, and clean up in the Form Unload

Form_Load

Set ConnObj = New ADODB.Connection
Set RstObj = New ADODB.RecordSet

End SUb

Form_Unload

Set RstObj = Nothing
Set ConnObj = Nothing

End Sub

In the remaining portions of the form, you should be able to use both these objects as needed. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Norris68]

If you are using an INSERT INTO statement you would be better off using a Command object instead of a Recordset as you will not be returning any records. Plus, the Command will tell you how many records were affected (i.e. added).