Hai I am Populating the data from MS Access to Excel Using VBA.I ill attach the original file with this question for your clarification.My Problem is now i am run the code in 2003 MS format during. We just moved to Windows 10 as a company and I have VBA code that uses and ODBC to connect and pull data from an oracle database. When I try to launch the code in Excel 2016 in windows 10 I was.
I am trying to get an ODBC connection to work from excel. I have copied the excel sheet from a working copy and am trying to point it at a different server.
I am a VBA noob and have no idea why it isn't working. Sheets('DataQuery').Select reportdate = ActiveSheet.Range('B1').Value Range('A3').Select With Selection.QueryTable.Connection = Array(Array( 'ODBC;DSN=DSN201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;DATABASE=Power;Network=' ), Array('DBMSSOCN;Address=111.11.2')).Sql = Array(reportdate).Refresh BackgroundQuery:=False End With I have not had any luck googling the '1004' error, it seems that it could be many things. Can anyone help, or point me in the direction of some good resources for setting the connection? Thanks RE: Run-time error '1004' General ODBC Error. Here is the SQL. When run on the server it doens't throw any errors. This is great code.
![General odbc error excel 2016 free General odbc error excel 2016 free](/uploads/1/2/5/4/125495729/190246022.png)
I ran it, took the string returned and copied and pasted it into my code. Here is the returned string ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;DATABASE=Power I copied it into my code like this Sheets('DataQuery').Select reportdate = ActiveSheet.Range('B1').Value Range('A3').Select With Selection.QueryTable.Connection = Array(Array( 'ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;DATABASE=Power;Network=' ), Array('DBMSSOCN;Address=111.11.2')).Sql = Array(reportdate).Refresh BackgroundQuery:=False End With Now I get 'Run time '1004' error SQL syntax error'. With the debugger still pointing to the.Refresh BackgroundQuery line. Could it have to do with the @start date being set both in the stored procedure and in the =exec spname line?
Thanks so much for your help. RE: Run-time error '1004' General ODBC Error (Programmer) 9 Sep 10 16:05. I am not sure what you mean by 'You did not return the entire string.' I even ran it a second time to see if the results were the same. The first time I did I got the above posted string repeated twice. This time I got the above string 5 times. What is missing from it?
I am using a dsn that is set up already and it works, but should I try setting up a new one for this test. I put your Array code back in and saved it this time.
Thanks RE: Run-time error '1004' General ODBC Error (Programmer) 9 Sep 10 19:04. Sheets('DataQuery').Select reportdate = ActiveSheet.Range('B1').Value Range('A3').Select With Selection.QueryTable.Connection = 'ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;DATABASE=Power;'.Sql = reportdate.Refresh BackgroundQuery:=False End WithFinally, what did you mean by, 'I put your Array code back in and saved it this time?' I posted NO Array code: rather suggested AGAINST using the Array function. Skip, Just traded in my old subtlety. For a NUANCE!
When running a query on a table in Access using a Data Source ODBC, users may periodically get an ODBC error: 'ORA-01013 User Requested Cancel of Current Operation.' When the user did not cancel the query. To fix this problem, the ODBC Driver settings need to be adjusted. Follow these steps:.
Click Start Settings Control Panel Administrative Tools Data Sources (ODBC). Click the 'System DSN' tab in the ODBC Data Source Administrator Window. Select the name of the Banner/Oracle data source from the list. Click the Configure button. On the Application tab of the Oracle ODBC Driver Configuration window, un-check all checkboxes with the exception of Read-Only Connection (refer to Figure 1).