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.