Excel/SQL Problem

[drinese18]

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

 

[dolswang]

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.

 

[drinese18]

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?

 

[dolswang]

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?









- Show quoted text -

You could simlify the SQL to:
stSQL1 = "SELECT * FROM index_master"

If that works, the problem was with the sql string.