Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Problem
I am having trouble connecting to an SQL database, my code can be seen below:
Sub Import_SQLData() Dim cnt As ADODB.Connection Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset Dim stSQL1 As String, stSQL2 As String Dim stConn As String Dim wbBook As Workbook Dim wsSheet1 As Worksheet Dim lnField As Long, lnCount As Long 'Instantiate the ADO-objects. Set cnt = New ADODB.Connection Set rst1 = New ADODB.Recordset Set rst2 = New ADODB.Recordset Set wbBook = ThisWorkbook Set wsSheet1 = wbBook.Worksheets(1) 'Path to the database. 'stDB = "http://151.108.114.146:1521" 'Create the connectionstring. stConn = "Provider = SQLOLEDB; DNS = DNSNAME; UserID = USERID; Password = PASSWORD; Data Source = DATASOURCENAME" 'The 1st raw SQL-statement to be executed. stSQL1 = "SELECT * FROM index_master WHERE index_id = indexID" 'The 2nd raw SQL-statement to be executed. stSQL2 = "SELECT * FROM index_master WHERE index_id = indexID" With cnt .Open (stConn) 'Open Connection .CursorLocation = adUseClient 'Necessary to disconnect the recordset. End With With rst1 .Open stSQL1, cnt 'Create the recordset. Set .ActiveConnection = Nothing 'Disconnect the recordset. End With With rst2 .Open stSQL2, cnt 'Create the recordset. Set .ActiveConnection = Nothing 'Disconnect the recordset. End With 'With wsSheet1 '.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset. '.Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset. 'End With 'Release objects from the memory. rst1.Close Set rst1 = Nothing rst2.Close Set rst2 = Nothing cnt.Close Set cnt = Nothing End Sub Can anyone shed some light on what I might be doing wrong, any help would be greatly appreciated, thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/SQL Problem
On Jan 23, 4:32*pm, drinese18
wrote: Well I think the indexID is declared, you mean if it's declared within the database? Or the code, I mean the problem I am having with the code is the connection string to the SQL database, that has so far been my only obstacle, so for the SQL statement all I have to do is put an and percent before the IndexID? " wrote: On Jan 23, 3:57 am, drinese18 wrote: I am having trouble connecting to an SQL database, my code can be seen below: Sub Import_SQLData() * * Dim cnt As ADODB.Connection * * Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset * * Dim stSQL1 As String, stSQL2 As String * * Dim stConn As String * * Dim wbBook As Workbook * * Dim wsSheet1 As Worksheet * * Dim lnField As Long, lnCount As Long * * *'Instantiate the ADO-objects. * * Set cnt = New ADODB.Connection * * Set rst1 = New ADODB.Recordset * * Set rst2 = New ADODB.Recordset * * Set wbBook = ThisWorkbook * * Set wsSheet1 = wbBook.Worksheets(1) * * *'Path to the database. * * 'stDB = "http://151.108.114.146:1521" * * *'Create the connectionstring. * * stConn = "Provider = SQLOLEDB; DNS = DNSNAME; UserID = USERID; Password = PASSWORD; Data Source = DATASOURCENAME" * * *'The 1st raw SQL-statement to be executed. * * stSQL1 = "SELECT * FROM index_master WHERE index_id = indexID" * * *'The 2nd raw SQL-statement to be executed. * * stSQL2 = "SELECT * FROM index_master WHERE index_id = indexID" * * With cnt * * * * .Open (stConn) 'Open Connection * * * * .CursorLocation = adUseClient 'Necessary to disconnect the recordset. * * End With * * With rst1 * * * * .Open stSQL1, cnt 'Create the recordset. * * * * Set .ActiveConnection = Nothing 'Disconnect the recordset. * * End With * * With rst2 * * * * .Open stSQL2, cnt 'Create the recordset. * * * * Set .ActiveConnection = Nothing 'Disconnect the recordset. * * End With * * 'With wsSheet1 * * * * '.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset. * * * * '.Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset. * * 'End With * * *'Release objects from the memory. * * rst1.Close * * Set rst1 = Nothing * * rst2.Close * * Set rst2 = Nothing * * cnt.Close * * Set cnt = Nothing End Sub Can anyone shed some light on what I might be doing wrong, any help would be greatly appreciated, thank you drines, Good question. Clean code. The code works fine with my database. I would check two things: 1. Security to connect to the db: VPN needed? Check password and username. (maybe use sa details). 2. The only thing i changed were the SQL's to : "SELECT * FROM index_master WHERE index_id =" & *indexID * * Is IndexID declared? Does the SQL work from in the db? Hope this helps. Works great with my db.- Hide quoted text - - Show quoted text - You could simlify the SQL to: stSQL1 = "SELECT * FROM index_master" If that works, the problem was with the sql string. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem viewing Excel 2003 Pivot Chart fields in Excel 2007 | Charts and Charting in Excel | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 | Excel Programming | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) |