Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error 3001 from excel to SQL Table (2000-sr1)

    have error 301 in:

    Set rs = Cn.OpenRecordset("SELECT * FROM [customers]", dbOpenDynaset)

    Sub test_sql()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim RowNo As Long
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "USER-E114319F02"
    Database_Name = "northwind"
    User_ID = ""
    Password = ""
    'SQLStr = "SELECT * FROM customers"

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    Set rs = Cn.OpenRecordset("SELECT * FROM [customers]", dbOpenDynaset)

    For RowNo = 2 To 100 ' Change to suit

    rs.AddNew

    rs.Fields(1) = Cells(RowNo, "A")
    rs.Fields(2) = Cells(RowNo, "B")
    rs.Fields(3) = Cells(RowNo, "C")

    rs.Update

    Next RowNo

    rs.Close
    Set rs = Nothing

    Cn.Close
    Set Cn = Nothing



    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error 3001 from excel to SQL Table (2000-sr1)

    You're mixing up ADO and DAO. Look up the Recordset object and its Open method in the ADO help.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error 3001 from excel to SQL Table (2000-sr1)

    have an example from excel to sql table..?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error 3001 from excel to SQL Table (2000-sr1)

    Try changing the line that gives you problems to

    rs.Open "SELECT * FROM Customers", Cn, adOpenKeyset, adLockOptimistic, adCmdText

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error 3001 from excel to SQL Table (2000-sr1)

    Perfect!

Posting Permissions

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