+ Reply to Thread
Results 1 to 2 of 2

runtime error: syntax error or access violation

  1. #1
    Registered User
    Join Date

    Unhappy runtime error: syntax error or access violation

    I'm getting the error for the following piece of code...plz help


    Sub itconfandscratch()

    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 SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "sturecord"
    Database_Name = "Scratch" ' Enter your database name here
    SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""

    rs.Open SQLStr, Cn, adOpenStatic
    ' Dump to spreadsheet

    Dim str2 As String

    str2 = "{"
    Dim i As Integer
    i = 0
    While (i < rs.RecordCount And i < 10)

    str2 = str2 + "'"
    str2 = str2 + rs(0)
    str2 = str2 + "'"
    str2 = str2 + ","
    rs.Move (1)

    i = i + 1


    str2 = str2 + "}"

    SQLStr = "SELECT [stud name],class,subject FROM dbo.stuconfig where [stud name] in " + str2

    Dim Cn1 As ADODB.Connection

    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset
    Server_Name = "cbvdhg-v"
    Database_Name = "exceptions"

    Set Cn1 = New ADODB.Connection
    Cn1.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""

    rs1.Open SQLStr, Cn1, adOpenStatic

    With Worksheets("sheet4").Range("a2:z1000") ' Enter your sheet name and range here
    .CopyFromRecordset rs

    End With
    ' Tidy up
    Set rs = Nothing
    Set Cn = Nothing

    End Sub


    The above is the entire code for getting a list of students from one database, storing it in a recordset and using this list to get the details of all these students from another database.
    Please suggest the solution for the error.


  2. #2

    RE: runtime error: syntax error or access violation

    You seem to be using a locally installed SQL Server. Your connection string

    Cn1.Open "Driver={SQL Server};Server=" & Server_Name & ";Database="
    & Database_Name & ""

    Normally you would expect to find

    in the connection string (where ? are the relevant parameters).

    If there is no UID or PWD, try with the defaults:


    appended to your connection string.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1