Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: SQL from Excel to MS SQL Server 7.0

    Hi

    Win2k, XP
    Excel 2k
    MS SQL Server 7.0


    I am having a problem updating my MS Server database with the code bellow. I think the problem is with this line:
    conn.ConnectionString = "Provider=SQLServer;

    can anybody help??
    see also the attached for additional info.
    here is the code in Excel
    Code:
    Sub INSERTDATAINDB()
       
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim AppPath, app As String
        
       AppPath = "c:\LOGCALL\test_Data.MDF"
       Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        conn.ConnectionString = "Provider=SQLServer;Data Source=" & AppPath & ";Persist Security Info=False"
        conn.ConnectionTimeout = 30
        conn.Open
        
        Set cmd.ActiveConnection = conn
        
        If Range("C" & CStr(ActiveCell.Row)).Value = "X" And Range("G" & CStr(ActiveCell.Row)).Value <> "DUPLICATE CALL" Then
            cmd.CommandText = "INSERT INTO LOGCALL_table VALUES ('" _
            & Range("B" & CStr(ActiveCell.Row)).Value & _
            "','" & Range("C" & CStr(ActiveCell.Row)).Value & _
            "',NULL,NULL,NULL,NULL,'" & Format(Range("H" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Format(Range("I" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Format(Range("J" & CStr(ActiveCell.Row)).Value, "HH:MM:SS") & _
            "','" & Range("C1").Value & "','" & Format(Date, "MM/DD/YYYY") & "');"
        End If
        cmd.Execute , , adCmdText
        conn.Close
    
    End Sub
    Attached Thumbnails Attached Thumbnails error.bmp  

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    is MDAC available or of the wrong version? what about using OLEDB Driver? instead

Posting Permissions

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