D
drinese18
I am trying to connect to a SQL database using the DAO connection, I tried
using ADO but it doesn't work so I switched to DAO you can view my code below:
Option Explicit
Sub Import_SQLData()
Dim cnt As DAO.Connection
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
Dim stDB As String, 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
Dim wrkODBC 'As String
'Dim Db As DAO.Connection
'Instantiate the ADO-objects.
'Set cnt = New DAO.Connection
'Set rst1 = New DAO.Recordset
'Set rst2 = New DAO.Recordset
'Set wbBook = ThisWorkbook
'Set wsSheet1 = wbBook.Worksheets(1)
'Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
'Set Db = wrkODBC.Connection("Spice conn", , ,
"ODBC;DSN=SPICE;UID=username;pwd=password;SERVER=server;")
'Path to the database.
stDB = "http://151.108.113.139"
'Create the connectionstring.
stConn = wrkODBC.Connection("Spice conn", , ,
"ODBC;DSN=SPICE;UID=username;pwd=password;SERVER=server;")
'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM index_master WHERE index_id = 36211"
'The 2nd raw SQL-statement to be executed.
stSQL2 = "SELECT * FROM index_master WHERE index_id = 3621"
With cnt
.Open (stConn) 'Open the 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
It basically keeps bringing up an error, I am more familiar with ADO than
DAO since its my preferred choice of usage, I'm not sure where I am going
wrong but maybe someone can help me here. The macro should basically download
data from certain tables within the database, any help would be appreciated,
Thank you
using ADO but it doesn't work so I switched to DAO you can view my code below:
Option Explicit
Sub Import_SQLData()
Dim cnt As DAO.Connection
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset
Dim stDB As String, 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
Dim wrkODBC 'As String
'Dim Db As DAO.Connection
'Instantiate the ADO-objects.
'Set cnt = New DAO.Connection
'Set rst1 = New DAO.Recordset
'Set rst2 = New DAO.Recordset
'Set wbBook = ThisWorkbook
'Set wsSheet1 = wbBook.Worksheets(1)
'Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
'Set Db = wrkODBC.Connection("Spice conn", , ,
"ODBC;DSN=SPICE;UID=username;pwd=password;SERVER=server;")
'Path to the database.
stDB = "http://151.108.113.139"
'Create the connectionstring.
stConn = wrkODBC.Connection("Spice conn", , ,
"ODBC;DSN=SPICE;UID=username;pwd=password;SERVER=server;")
'The 1st raw SQL-statement to be executed.
stSQL1 = "SELECT * FROM index_master WHERE index_id = 36211"
'The 2nd raw SQL-statement to be executed.
stSQL2 = "SELECT * FROM index_master WHERE index_id = 3621"
With cnt
.Open (stConn) 'Open the 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
It basically keeps bringing up an error, I am more familiar with ADO than
DAO since its my preferred choice of usage, I'm not sure where I am going
wrong but maybe someone can help me here. The macro should basically download
data from certain tables within the database, any help would be appreciated,
Thank you