Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- oucsester ------------------------------------------------------------------------ oucsester's Profile: http://www.excelforum.com/member.php...o&userid=34082 View this thread: http://www.excelforum.com/showthread...hreadid=538434 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
syntax error in VBA in query from Access | Excel Discussion (Misc queries) | |||
runtime error: syntax error or access violation | Excel Programming | |||
Microsoft][ODBC SQL Server Driver]Syntax error or access violation | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |