+ Reply to Thread
Results 1 to 2 of 2

runtime error: syntax error or access violation

  1. #1
    Registered User
    Join Date
    05-03-2006
    Posts
    1

    Unhappy runtime error: syntax error or access violation

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

    [VBA]

    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

    Wend

    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
    .ClearContents
    .CopyFromRecordset rs

    End With
    ' Tidy up
    rs1.Close
    Set rs = Nothing
    Cn1.Close
    Set Cn = Nothing
    rs.Close
    Cn.Close

    End Sub

    [VBA]

    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.

    Thanks,
    Oucsester

  2. #2
    AA2e72E
    Guest

    RE: runtime error: syntax error or access violation


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

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

    Normally you would expect to find
    ";UID=?;PWD=?;"

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

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

    ";UID=sa;PWD=;"

    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)

Bookmarks

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