Run-time error '1004' General ODBC Error 1

[Imakeoil]

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.221.70,1433"))
.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

 

[SkipVought]

This is a very general error. Your SQL proabaly has an error.

Please post your SQL.

To verify, open the Immediate Window (see the View menu) and type in...

?Array(reportdate)

and hit ENTER.

COPY the resulting code and then on your sheet, EDIT the QueryTable and PASTE your copied SQL into the SQL window (see the SQL button in MS Query) If your SQL runs, then there is another problem.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oh and one other thing: What VERSION of Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Imakeoil]

Here is the SQL. When run on the server it doens't throw any errors.

ALTER PROCEDURE [dbo].[jpm_tiretrack] (@endd int) AS

--Altered to report all trucks 11-25-09


/*
DECLARE @sDate datetime, @eDate datetime, @startd int, @endd int
SET @sDate = '2010-01-01'
SET @eDate = '2010-01-28'
SET @startd = 28579 --(SELECT shiftindex FROM hist_exproot WHERE shiftdate = @sDate AND shift# = '1')
SET @endd = (SELECT shiftindex FROM hist_exproot WHERE shiftdate = @eDate AND shift# = '2')
*/


Declare @startd int
Set @startd = 28579


SELECT eqmt AS truck, (SUM(duration) / 3600.0) AS hours
INTO #event_data
FROM PowerView.dbo.hist_statusevents
INNER JOIN PowerView.dbo.hist_exproot ON PowerView.dbo.hist_exproot.shiftindex = PowerView.dbo.hist_statusevents.shiftindex
WHERE PowerView.dbo.hist_exproot.shiftindex BETWEEN @startd AND @endd --AND eqmt LIKE 'T1%'
AND Powerview.dbo.DefineTruckCompany (eqmt) = 'Albian' AND category = '1'
--AND eqmt LIKE 'T157'
GROUP BY eqmt

-- get distance and load data for the 797 fleet.
SELECT hist_loads.truck, (SUM(disteh + distfh) / 1000.0) AS TKM, (SUM(loadtons) / 2.08) AS VOL
INTO #cycle_data
FROM PowerView.dbo.hist_loads
INNER JOIN PowerView.dbo.hist_exproot ON PowerView.dbo.hist_exproot.shiftindex = PowerView.dbo.hist_loads.shiftindex
INNER JOIN #event_data ON #event_data.truck = hist_loads.truck
WHERE PowerView.dbo.hist_exproot.shiftindex BETWEEN @startd AND @endd
GROUP BY hist_loads.truck

-- group temp. tables.
SELECT #event_data.truck, hours, tkm, vol
FROM #event_data
INNER JOIN #cycle_data ON #cycle_data.truck = #event_data.truck
ORDER BY #event_data.truck

DROP TABLE #event_data
DROP TABLE #cycle_data


When I typed ?Array(reportdate)into the immediate window it gave me a type mismatch error. When I held my cursor over 'reportdate' it showed reportdate="exec jpm_tiretrack '12/01/2002', '09/01/2010'"

The excel version is 2000 and the server is sql server 2005.

Thanks

 

[SkipVought]

You don't really need the Array() function. It is clumsy!

Try...

Sheets("DataQuery").Select
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.221.70,1433"))
    .Sql = [b]ActiveSheet.Range("B1").Value[/b]
    .Refresh BackgroundQuery:=False
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Imakeoil]

No go. This still throws a '1004' error. Could it be the port? As I understand it the ports tend to be standard, but I couldn't figure out how to check the settings on this server.

 

[SkipVought]

OK.

In a new sheet, start a new database query, format an SQL and return data to Excel.

With this sheet active, toggle to the VB Editor and run this procedure...

sub WhatQT_Properties()
  with activesheet.QueryTables(1)
    debug.print .connection
    debug.print ""
    debug.print .connection
  end with
end sub

Open the Immediate Window from the View menu. You will see your connection string and SQL.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Imakeoil]

This is great code.

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.221.70,1433"))
.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 sp_name line?

Thanks so much for your help.

 

[SkipVought]

Here is the returned string
ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;DATABASE=Power

You did NOT return the entire string.

Get rid of the Array() functions also!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Imakeoil]

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

 

[SkipVought]

The string you copied
ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;
DATABASE=Power

The string in your code

ODBC;DSN=dsn201;Description=JPdb;UID=minedata;PWD=1xx$F;APP=Microsoft® Query;WSID=CAL-L-97076;
DATABASE=Power;Network=DBMSSOCN;Address=111.11.221.70,1433

So if what you say is true, then replace the string in your code with THIS string.

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 With

Finally, 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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]