Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    open database and perform (Access 2000)

    I want to open a database from another database,open a table in datasheet view and perform some action on a given field.I have the following function

    Dim cat As New ADOX.Catalog, objConn As New ADODB.Connection
    objConn.Open "Provider= Microsoft.Jet.OLEDB.4.0;Data Source = C:bestorebe.mdb"
    With cat
    .ActiveConnection = objConn
    ..?
    End With
    How can i update the field quantity in the table Items in
    the database BE ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open database and perform (Access 2000)

    You don't need a catalog object for that. A command object will do nicely. Here's a function that will execute a query on your remote database, and all you need to do is pass it a connection object you're creating in your existing code, plus the SQL string for the query.

    <pre>Private Function ExecuteSQL(cnn As ADODB.Connection, _
    ByVal strSQL As String) As Boolean
    <font color=448800>'Created by Charlotte Foust 4/2000
    'execute an action query and return
    'a flag for success of execution </font color=448800>
    Dim lngRecs As Long <font color=448800>'holds count of records affected</font color=448800>
    Dim cmd As ADODB.Command <font color=448800>'holds ADO Command object</font color=448800>
    Dim errCurr As ADODB.Error 'holds each ADO error
    On Error Resume Next

    <font color=448800>'initialize the command object</font color=448800>
    Set cmd = New ADODB.Command

    <font color=448800>'set command object properties
    'and execute the command</font color=448800>
    With cmd
    .ActiveConnection = cnn
    .CommandText = strSQL
    .CommandType = adCmdText
    .Execute lngRecs, , adCmdText
    End With <font color=448800>'cmd</font color=448800>

    If cnn.Errors.Count >0 Then
    For each errCurr in cnn.Errors
    MsgBox errCurr.Number & "--" & errCurr.Description
    Next errCurr
    cnn.Errors.Clear
    ElseIf Err <> 0 Then
    ' Err.raise Err.Number, mcstrModName & ".ExecuteSQL", _
    "An error occurred in executing the SQL: " _
    & vbCrLf & Err.Description
    MsgBox Err.Number & "--" & Err.Description & vbCrLf & mcstrModName
    Else
    ExecuteSQL = CBool(lngRecs)
    End If

    <font color=448800>'destroy object variable and exit</font color=448800>
    Set cmd = Nothing
    End Function <font color=448800>'ExecuteSQL(cnn As ADODB.Connection, _
    ByVal strSQL As String) As Boolean</font color=448800></pre>

    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •