Hello,
I am modifying the macro in Excel, which was written by my colleague before he quits the company. I have been assigned to continue coding the macro in Excel. The objective of this macro would be " get the required data from a local DB by calling the SQL query and plot the graph based on the data gathered " .
I am quite new to this environment and I am slowly learning the VBA scripts coded by my colleague. When I run the macro, I am getting the Run time error
"Run Time Error 3146 ODBC - call failed". I searched in the internet to resolve this problem, but I couldn't. May be if I am aware of those kind of Excel macros, I could have solved this run time error.
I have pasted below the code
Public FileContents As String 'global variable to contain the SQL query string
Public RowCounter As Integer
Public ColCounter As Integer
Public RowCounter1 As Integer
Public ColCounter1 As Integer
Const FTP_ip = "10.68.20.158"
Private Sub CommandButton1_Click()
Dim strDBName As String
Dim strDBUser As String
Dim strDBPwd As String
Dim strDSNName As String
Dim strConnectionStr As String
Dim strQueryFile_path As String 'filepath of SQL Query file
Dim wrkODBC As Workspace
Dim conDb As Connection
Dim rstTemp As Recordset
strDBName = "tpfdb"
strDBUser = "root"
strDBPwd = "admin"
strDSNName = "tpfdatabase"
strQueryFile_path = ThisWorkbook.Path & "\Queries\"
' Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", strDBUser, strDBPwd, dbUseODBC)
' Open read-only Connection object based on information
' you enter in the ODBC Driver Manager dialog box.
strConnectionStr = "ODBC;DATABASE=" + strDBName + ";UID=" + strDBUser + ";PWD=" + strDBPwd + ";DSN=" + strDSNName
Set conDb = wrkODBC.OpenConnection("localdb", dbDriverComplete, False, strConnectionStr)
Set rstTemp = conDb.OpenRecordset("SET GLOBAL sql_mode='ANSI';", dbOpenDynamic)
I am getting the error while executing the command highlighted above. As I said i am new to this environment, can anyone help me to resolve this?
Also it would be great if anyone can give me some reference/websites that tells about ODBC in Excel.
Many Thanks in Advance!!!!!!!!!!
----------------------------------------------------------
Regards,
Hari.
I am modifying the macro in Excel, which was written by my colleague before he quits the company. I have been assigned to continue coding the macro in Excel. The objective of this macro would be " get the required data from a local DB by calling the SQL query and plot the graph based on the data gathered " .
I am quite new to this environment and I am slowly learning the VBA scripts coded by my colleague. When I run the macro, I am getting the Run time error
"Run Time Error 3146 ODBC - call failed". I searched in the internet to resolve this problem, but I couldn't. May be if I am aware of those kind of Excel macros, I could have solved this run time error.
I have pasted below the code
Public FileContents As String 'global variable to contain the SQL query string
Public RowCounter As Integer
Public ColCounter As Integer
Public RowCounter1 As Integer
Public ColCounter1 As Integer
Const FTP_ip = "10.68.20.158"
Private Sub CommandButton1_Click()
Dim strDBName As String
Dim strDBUser As String
Dim strDBPwd As String
Dim strDSNName As String
Dim strConnectionStr As String
Dim strQueryFile_path As String 'filepath of SQL Query file
Dim wrkODBC As Workspace
Dim conDb As Connection
Dim rstTemp As Recordset
strDBName = "tpfdb"
strDBUser = "root"
strDBPwd = "admin"
strDSNName = "tpfdatabase"
strQueryFile_path = ThisWorkbook.Path & "\Queries\"
' Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", strDBUser, strDBPwd, dbUseODBC)
' Open read-only Connection object based on information
' you enter in the ODBC Driver Manager dialog box.
strConnectionStr = "ODBC;DATABASE=" + strDBName + ";UID=" + strDBUser + ";PWD=" + strDBPwd + ";DSN=" + strDSNName
Set conDb = wrkODBC.OpenConnection("localdb", dbDriverComplete, False, strConnectionStr)
Set rstTemp = conDb.OpenRecordset("SET GLOBAL sql_mode='ANSI';", dbOpenDynamic)
I am getting the error while executing the command highlighted above. As I said i am new to this environment, can anyone help me to resolve this?
Also it would be great if anyone can give me some reference/websites that tells about ODBC in Excel.
Many Thanks in Advance!!!!!!!!!!
----------------------------------------------------------
Regards,
Hari.
