Hi, i am using a ado object and a recordset object to connect to my database.
this is working fine, but i am having to type in the following lines all the
time:

Dim adoConnection As ADODB.connection
Dim adoRecordset As ADODB.Recordset

    Set adoConnection = New ADODB.connection
    Set adoRecordset = New ADODB.Recordset

'build our connection string to use when we open the connection

    connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Data Source=C:\Documents and Settings\My
Documents\Database.mdb"

'open the connection  with the above string
    adoConnection.Open connectString

so i decided to put this code in a module withina sub routine. so now i am
trying to call it from various places and i am getting an error. how do i go
about connecting to my database without having to type in the same code, and
just call it.

regards

What's the error?  What the function look like?  I understand what you want
to accomplish but not the exact functionality you are looking for.

Are you looking to encapsulate all db connections in a standard module or
specific functions?

Options:
- You could create specific functions in a standard module - like
DeleteRecord (pass the table and records ID to the method)
- Depending on your application and how dependant it is on the DB you could
create a connection, in a module, and keep it alive for the duration of the
applications life  (note: this is not something I would recommend but
listing as an option.  DB apps and connections I use the philosophy of
"create at the last minute and break as soon as you can")

Show quoteHide quote

Error: expected variable or procudure not module. I am looking to encapsulate
all connections in one module

this is how i am trying to connect:

Private Sub Form_Load()

Call connection

adoRecordset.Open ("Select * From Product"), adoConnection, ,
adLockPessimistic

etc....


Show quoteHide quote

My own preference is to use one, and only one, connection to the
database. This makes it easy to disconnect when closing the application.
Further, you'll find that if you're connecting and reconnecting every
time you need to run a query, there's a discernable performance hit if
you have several successive queries if running over a slow LAN (say
you're connected at 54Mbs wirelessly).

That said, I understand that ADO.NET is designed to drop the connection
rather than keep it open -- so each to their own.

Anyway, my methodology is as below, always use the public connection
object "cn" for all my command and recordset objects, checking that it's
connected each time it's used by calling ADO_ConnectToDB. You'll need to
change take out the encryption stuff.

Public cn As New ADODB.Connection

Public Function ADO_ConnectToDB() As Boolean

    'Check whether the connection is already open.
    If Not cn Is Nothing Then
       If cn.State <> adStateClosed Then
          ADO_ConnectToDB = True
          Exit Function
       End If
    End If

    'Open connection the database
    With cn
       .Errors.Clear
       On Error Resume Next
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Properties("Jet OLEDB:System database") = myWORKGROUPPATH _
                & "\" & myWORKGROUPFILENAME
       .Open "Data Source=" & myDATABASEPATH & "\" _
                            & myDATABASEFILENAME & ";" _
             & "User Id=" & fcnDecryptMe(DBUserName) & ";" _
             & "Password=" & fcnDecryptMe(DBPassword) & ";"

       If .Errors.Count = 0 And Not cn Is Nothing Then
          ADO_ConnectToDB = True
       Else
          MsgBox "Failed to connect to DB: " & vbCrLf & vbCrLf _
                 & .Errors(0).Description & vbCrLf _
                 & myDATABASEPATH & "\" & myDATABASEFILENAME _
                & ": " & IIf(Dir(myDATABASEPATH & "\" _
                & myDATABASEFILENAME) = "", "MISSING", "EXISTS") _
                & vbCrLf _
                 & myDATABASEPATH & "\" & myWORKGROUPFILENAME _
                 & ": " _
                & IIf(Dir(myDATABASEPATH & "\" & myWORKGROUPFILENAME) _
                  = "", "MISSING", "EXISTS"), vbCritical
         On Error GoTo 0
       End If
    End With

End Function


Pinto1uk wrote:
Show quoteHide quote

www Link in VB 6...
Change language keyboard
missing reference
Run-time error 3706
System Error &H80004015 (-2147467243) running VB6 IDE ActiveX DLL
Intercepting File read/write request from any program
active desktop
Database connection/Access help
Form Event
Show all connections