Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use this code to open a table in a workbook... but have error whenthe code go:

    RS1.Open [ANA_AGENZIE$], CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    complete code:
    Sub DBOpenCreate1()

    Dim CN1 As ADODB.Connection
    Dim RS1 As ADODB.Recordset

    On Error GoTo errore

    Set RS1 = New ADODB.Recordset
    Set CN1 = New ADODB.Connection
    CN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\nsd01d9500\mercati\Rete Territoriale\Animazione Sportello\SERVIZIO\ANA_AGENZIE.XLS;Extended Properties=Excel 8.0;"

    RS1.Open [ANA_AGENZIE$], CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    Exit Sub

    errore:
    MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & "Descrizione: " & Err.Description & _
    vbCrLf & "Sorgente dell'Errore: " & Err.Source

    Set RS1 = Nothing
    Set CN1 = Nothing

    Err.Clear

    End Sub

    note:
    Workbook and sheet have the same name.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The first argument of RS1.Open should be a string (between quotes).


  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The first argument of RS1.Open should be a string (between quotes).
    ????
    Correct?


    Sub DBOpenCreate1()

    Dim CN1 As ADODB.Connection
    Dim RS1 As ADODB.Recordset
    Dim NAME_SHEET As String
    On Error GoTo errore

    Set RS1 = New ADODB.Recordset
    Set CN1 = New ADODB.Connection
    CN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\nsd01d9500\mercati\Rete Territoriale\Animazione Sportello\SERVIZIO\ANA_AGENZIE.XLS;Extended Properties=Excel 8.0;"
    NAME_SHEET = "ANA_AGENZIE"
    RS1.Open " & [NAME_SHEET$] & ", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    Exit Sub

    errore:
    MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & "Descrizione: " & Err.Description & _
    vbCrLf & "Sorgente dell'Errore: " & Err.Source

    Set RS1 = Nothing
    Set CN1 = Nothing

    Err.Clear

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, if you use a variable, use

    NAME_SHEET = "[ANA_AGENZIE$]"
    RS1.Open NAME_SHEET, CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    No, if you use a variable, use

    NAME_SHEET = "[ANA_AGENZIE$]"
    RS1.Open NAME_SHEET, CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    OK, now work!
    But why the code open the workbook!!!
    I need to use the ADO connection to Excel workbook without open it from the server dir!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code shouldn't open the workbook in Excel. But it will lock it as in use until the recordset is closed.


  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    No, if you use a variable, use

    NAME_SHEET = "[ANA_AGENZIE$]"
    RS1.Open NAME_SHEET, CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have only quoted my reply...

Posting Permissions

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