ODBC Connection Fail
-
Greets.
This may or not be a good forum group to start in, if not, apologies.
Microsoft had no solution for this:
I have an Excel File, a Access Query, and a SQL Based Database. The
relation? The Access Query has both native and polled/linked tables to the
SQL native backend. A Query is designed in Access (for ease) to poll the SQL
side through the linked tables, and a couple of other crosstab queries. This
Access Query needs to be accessed from Excel Pivot Table Manager as a link to
an outside source.
The solution... a user can refresh their pivot table using ONLY Excel
collecting data from both Access AND SQL.
The method... use a Excel Pivot Table Manager.
The problem... "ODBC connection failed" to the SQL backend.
Hypothesis... Excel cannot pull third source data through the Access layer.
ODBC Connections have been verified that they exist and function for both
Access and the SQL side on the PC attempting this feat.
Environment: Dozens of queries have been designed in access as a primary
mode of data extraction from the SQL side. In fact, the primary interface to
the data is actually programmed through VB in Access. (Not my fault). The
problem is that we have layers of experienced users, primarily the ones
responsible for queries are only capable of designing them through Access.
The final End User for the Pivot tables is not allowed access to the queries,
for stability concerns, and lack of End Users experience in anything
complicated.
It is vital that the interaction for the End User is near zero to obtain the
data, and I have to work with the query writing person to pull things into
access.
Microsoft support has not been able to either fully understand or workaround
this issue.
Please e-mail me/ respond if you have additional questions for resolving
this. I am prepared to offer a graphic representation of this if necessary.
-
Try saving most of the Access queries in MSQuery queries...
The Excel users might also need to have the exact same ODBC dsn's defined on their computers as the people who created the queries on the ODBC linked SQL Server tables in Access in the first place.
Most of Access SQL's syntax can also be used directly in the MS-Query applet that Excel can fire up...but MS Query isn't installed by default anymore. Once you get it installed, copy the SQL for each Access query you want to use into the MSQuery's SQL space, connecting to the same ODBC datasource that was used to link the tables with in Access.
Or, save the queries in SQL Server views instead. Again, most of Access' SQL syntax is usable in SQL Server 7/2000/2005 straight up (Access pivot queries are not, nore are VBA functions, though, but SS2005 has a similar way to do it. WITH CUBE could also be helpful, esp. for pivot table source queries).
-
If you're using the "wizard" for Excel connectivity this is exactly what is happening, I'm afraid it hardwires the data source of the machine you're using.
Also, I really couldn't agree with corey more on this one, why are you using Access? Why introduce a flaky piece of dross into your solution when there's absolutely no need to do this? Yes, you can use queries built in Access Query builder and (pretty much, usually) cut and paste them directly into SQL, but from experience query builder puts a huge amount of rubbish into them anyway. Use stored procedures. If you're really not comfortable with coding them by hand then there is quite a good query builder in SQL 2000 itself......
Here's the solution we use for setting up data sources to Excel, it isn't perfect 'cos you have to hardwire the password into the app, but as long as you use a relatively harmless password (i.e. one that can only access and execute stored procedures) then you should be fine.....
This lot needs attaching to a form - I'll send you the actual spreadsheet if you want it, just send your email address in a private message.
Public Enum LTServers
LaserTracker = 1
End Enum
Public Enum DatabaseType
SQL_Server = 0
Oracle = 1
End Enum
Dim SvrType, DbName, SvrName, Uid, Pass As String
Dim jo() As String
Function Get_Connection_String(DB As DatabaseType, svr As String, Uid As String, pwd As String, DbName As String) As String
Dim Server As String
Dim DataBase As String
Dim CS As String
Select Case DB
Case 0 ' SQL SERVER
CS = "driver={SQL Server};server=" & svr & ";uid=" & Uid & ";pwd=" & pwd & ";database=" & DbName
'CS = "DSN=SQLServer;UID=sa;PWD=;"
Case 1 ' ORACLE
CS = "Provider=MSDASQL;driver={Microsoft ODBC for Oracle};server=" & DbName & ";uid=" & Uid & ";pwd=" & pwd & ";"
End Select
Get_Connection_String = CS
End Function
Sub ClearWorksheet(ByRef ws As Worksheet)
ws.UsedRange.ClearContents
End Sub
Public Function ParseDataSource(DS As String)
Dim j As Integer
jo = Split(DS, ";")
'j = InStr(1, jo(0), "=", vbTextCompare)
jo(0) = Mid(jo(0), InStr(1, jo(0), "=", vbTextCompare) + 1, Len(jo(0))) '
jo(1) = Mid(jo(1), InStr(1, jo(1), "=", vbTextCompare) + 1, Len(jo(1))) '
jo(2) = Mid(jo(2), InStr(1, jo(2), "=", vbTextCompare) + 1, Len(jo(2))) 'DatabaseName
jo(3) = Mid(jo(3), InStr(1, jo(3), "=", vbTextCompare) + 1, Len(jo(3))) 'DatabaseName
jo(4) = Mid(jo(4), InStr(1, jo(4), "=", vbTextCompare) + 1, Len(jo(4))) 'DatabaseName
'MsgBox jo(0)
'MsgBox jo(1)
'MsgBox jo(2)
'MsgBox jo(3)
'MsgBox jo(4)
End Function
Sub Get_DataSourceList()
Dim dbType As String
Dim xlsheet As Worksheet
Set xlsheet = Worksheets("setup")
frmSetup.cboDataSource.Clear
'Set cmd = Get_Command("Select * from DataSources")
'cmd.Open "driver={SQL Server};server=TUKDB002;uid=sa;pwd=sa;database=LaserTracker"
'Set rs = cmd.Execute("Select * from DataSources")
For i = 3 To 200
If Len(xlsheet.Cells(i, 1).Value) > 1 And Not xlsheet.Cells(i, 1).Value = "END" Then
ParseDataSource xlsheet.Cells(i, 1).Value
If jo(0) = "{SQL Server}" Then
jo(0) = "SQL_Server"
With frmSetup.cboDataSource
.AddItem jo(0) & " - " & jo(1) & " - " & jo(2) & " - " & jo(3) & " "
.List(.ListCount - 1, 1) = jo(0) 'DB Type
.List(.ListCount - 1, 2) = jo(1) 'ServerName
.List(.ListCount - 1, 3) = jo(4) 'Database Name
.List(.ListCount - 1, 4) = jo(2) 'userid
.List(.ListCount - 1, 5) = jo(3) 'password
End With
Else
jo(0) = "Oracle"
With frmSetup.cboDataSource
.AddItem jo(0) & " - " & jo(1) & " - " & jo(2) & " - " & jo(3) & " "
.List(.ListCount - 1, 1) = jo(0) 'DB Type
.List(.ListCount - 1, 2) = "" 'jo(2) 'ServerName
.List(.ListCount - 1, 3) = jo(2) 'Database Name
.List(.ListCount - 1, 4) = jo(3) 'userid
.List(.ListCount - 1, 5) = jo(4) 'password
End With
End If
End If
'frmSetup.cboDataSource.AddItem rs(0).Value
Next
End Sub
Sub Get_ProcedureList()
On Error GoTo 1
Dim cmd As New ADODB.Connection
Dim rs As New ADODB.Recordset
If Not frmSetup.cboDataSource.ListIndex >= 0 Then Exit Sub
frmSetup.cmbProcList.Clear
Select Case frmSetup.cboDataSource.List(frmSetup.cboDataSource.ListIndex, 1)
Case "SQL_Server" ' SQL Server
cmd.Open Get_Connection_String(frmSetup.cboServerType.ListIndex, frmSetup.txtServerName.Text, frmSetup.txtUserID.Text, frmSetup.txtPassword.Text, frmSetup.txtDatabaseName.Text)
Set rs = cmd.Execute("SELECT o.name FROM dbo.sysobjects o where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1) and o.name not like N'#%%' and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 order by o.name")
Do While Not rs.EOF
frmSetup.cmbProcList.AddItem rs(0)
rs.MoveNext
Loop
rs.Close
cmd.Close
Case "Oracle" ' Oracle
cmd.Open Get_Connection_String(frmSetup.cboServerType.ListIndex, frmSetup.txtServerName.Text, frmSetup.txtUserID.Text, frmSetup.txtPassword.Text, frmSetup.txtDatabaseName.Text)
Set rs = cmd.Execute("Select Owner, View_Name from ALL_VIEWS WHERE OWNER = 'TLS' ")
Do While Not rs.EOF
frmSetup.cmbProcList.AddItem rs(0) & "." & rs(1)
rs.MoveNext
Loop
rs.Close
cmd.Close
Case Else
MsgBox frmSetup.cboDataSource.List(frmSetup.cboDataSource.ListIndex, 1)
End Select
Exit Sub
1
MsgBox Err.Description
End Sub
'*********** Procedures For Connecting TO a database *******
Function Get_Connection() As ADODB.Connection
Dim Cn As New ADODB.Connection
Dim CS As String
'''On Error Resume Next
CS = Get_Connection_String(frmSetup.cboServerType.ListIndex, frmSetup.txtServerName.Text, frmSetup.txtUserID.Text, frmSetup.txtPassword.Text, frmSetup.txtDatabaseName.Text)
Cn.ConnectionTimeout = 5
If CS = "OFFLINE" Then
Cn.Close
Else
Cn.Open CS
''Msgbox CN.State
End If
Set Get_Connection = Cn
End Function
Function Get_Connection2() As ADODB.Connection
Dim Cn As New ADODB.Connection
Dim CS As String
Dim xlsheet2 As Worksheet
Set xlsheet2 = Worksheets("Setup")
CS = xlsheet2.Cells(1, 1).Value
Cn.ConnectionTimeout = 5
If CS = "OFFLINE" Then
Cn.Close
Else
Cn.Open CS
''Msgbox CN.State
End If
Set Get_Connection2 = Cn
End Function
Function Get_Command2(SP As String) As ADODB.Command
Dim Cn As ADODB.Connection
Dim cmd As New ADODB.Command
'''On Error Resume Next
cmd.ActiveConnection = Get_Connection2()
cmd.CommandText = SP
cmd.CommandType = adCmdStoredProc
Set Get_Command2 = cmd
End Function
Function Get_Command(SP As String) As ADODB.Command
Dim Cn As ADODB.Connection
Dim cmd As New ADODB.Command
'''On Error Resume Next
cmd.ActiveConnection = Get_Connection()
cmd.CommandText = SP
cmd.CommandType = adCmdStoredProc
Set Get_Command = cmd
End Function
'******************************************************************************
Function Get_Record_Set(SP As String) As ADODB.Recordset
Dim cmd As New ADODB.Command
On Error Resume Next
Set cmd = Get_Command(SP)
Set Get_Record_Set = cmd.Execute()
End Function
'******************************************************************************
'***STORED PROCEDURES**********************************************************
--This is where you define the stored procedures you want to call
Function sp_lm_laser_planning_PO_Status(Server As LTServers, PO_NO As Integer) As Integer
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim ret_val As Integer
On Error Resume Next
Set cmd = Get_Command(Server, "sp_lm_laser_planning_PO_Status")
Set prm1 = cmd.CreateParameter("Production Order No", adInteger, adParamInput, , PO_NO)
cmd.Parameters.Append prm1
Set rs = cmd.Execute
If rs.State = 1 Then
ret_val = rs(0).Value
rs.Close
Else
ret_val = 0
End If
cmd.ActiveConnection.Close
sp_lm_laser_planning_PO_Status = ret_val
End Function
Function lm_sp_laser_planning_get_item_name(Server As LTServers, Item_code As String) As String
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim ret_val As String
On Error Resume Next
Set cmd = Get_Command(Server, "lm_sp_laser_planning_get_item_name")
Set prm1 = cmd.CreateParameter("Item Code", adChar, adParamInput, 10, Item_code)
cmd.Parameters.Append prm1
Set rs = cmd.Execute
If rs.State = 1 Then
ret_val = rs(1).Value
rs.Close
Else
ret_val = ""
End If
cmd.ActiveConnection.Close
lm_sp_laser_planning_get_item_name = Trim(ret_val)
End Function
Function lm_sp_laser_planning(Server As LTServers) As ADODB.Recordset
Dim rs As New ADODB.Recordset
'On Error Resume Next
Set rs = Get_Record_Set(Server, "lm_sp_laser_planning")
Set lm_sp_laser_planning = rs
End Function
Function sp_lm_get_item_list(Server As LTServers) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Set rs = Get_Record_Set(Server, "sp_lm_get_item_list")
Set sp_lm_get_item_list = rs
End Function
-
I already responded to Richard directly, and here is a portion of my response.
Corey, I think you might be closer to the solution. I will attempt to recreate the SQL statements directly into MS-Query. I don't think I have tried that completely yet.
If this works though, I think I may have a more interested challenge in automating the conversion from the SQL generated code in Access (which b.t.w. I agree with Richard on the overhead, but hey, if it works consistently....) to MS-Query, whenever the Access Query programmer decides he wants a different lookup criteria.
Please feel for me here folks.... I would definitely prefer to do this *ALL* in SQL, since that is the native format of the backend data, but alas, shifting paradigms and teaching people SQL is a much bigger immediate challenge.
Response to Richard as Follows:
You see, the Pivot table is connected to an external connection throught the ODBC driver for Access database objects.
The Access Database object has a crosstab query that I need to have excel pivot.
In the Access Database, the query calls other sub-queries, that are tied with linked tables.
The linked tables are linked to the SQL backend.
I can access the Access queries that are only dealing with non-externally linked table (i.e. Access native tables) -OR- directly to the SQL tables and queries.
I believe what I really need is a ODBC driver that can -chain- multiple connections with thier proper authentications.
For my personal level of programming, I can poll datasources based on programmatical sql statements, but the queries are already made and maintained throught the Access query editor.
Coding a solution would be great if there were only one or two queries I had to replicate in .NET language (specifically VB.NET), but there are many, so I need to work with the applications themselves, and also make it easy for non-programmers to continue to design queries in Access.
-
Okay, this didn't work out right. Here's why:
This is the "Master" Select Statement that I need to run in the Query editor, Most of this accesses the SQL backend:
SELECT tblInItem.ItemId, tblInItem.Descr, tblInItemAddlDescr.AddlDescr, [IN - On Hand Qty].onhand
FROM (tblInItem LEFT JOIN tblInItemAddlDescr ON tblInItem.ItemId = tblInItemAddlDescr.ItemId) LEFT JOIN [IN - On Hand Qty] ON tblInItem.ItemId = [IN - On Hand Qty].ItemId
WHERE (((tblInItem.ItemStatus)=1) AND ((tblInItem.SalesCat)="PR") AND ((tblInItem.UsrFld1)="yes"))
ORDER BY tblInItem.ItemId;
Notice that the " [IN - On Hand Qty].onhand " is actually a reference to another query in ACCESS:
SELECT Sum([qty]-[invoicedqty]-[removeqty]) AS onhand, tblInQtyOnHand.ItemId
FROM tblInQtyOnHand
GROUP BY tblInQtyOnHand.ItemId
Two issues exist here. First, since I am loading the ODBC driver for the SQL backend, the [IN - On Hand Qty].onhand is fouling up due to the fact it in is an ACCESS table.
Secondly, although I could theoretically combine the two, (I am not quite sure yet how to nest select statements) I would have to do this for ALL the items I am importing into Excel, some of which have SEVERAL ties to Access tables that I would have to nest into the main SQL statement.
Sure it is job security that I would become a doggon expert at writing nested SQL statements, and I wouldn't mind doing it if I didn't have a network to run as well, there HAS to be another alternative like a nested odbc connection that can take tables from two completely different sources.
Please help obi-wan, you're our only hope!!!
-
I've lost the thread of this topic and only read your most recent post....
You could, although it's probably ugly, have SQL Server have a linked server back to your Access DB. Then you could write the entire query in SQL Server's universe which can access the Access DB via the linked server mechanism... I'm not sure if this fits in with the rest of your post regarding Excel + Access + SQL - there's surely got to be a smoother way of doing things....... Good luck!
-
I thought of that as well Ian. Thanks for that though. The problem on that is that the owner of the queries is only familiar with Access, and is an Accessite (new word for Access Fanatic)
For this moment, I am wondering if someone could give me a quick example of nesting select statements in SQL until we can figure this out a better way through Excel + Access + SQL .
-
Tobias,
Rewriting your query as a nested one would look like this:
SELECT tblInItem.ItemId, tblInItem.Descr, tblInItemAddlDescr.AddlDescr, [IN - On Hand Qty].onhand
FROM (tblInItem LEFT JOIN tblInItemAddlDescr ON tblInItem.ItemId = tblInItemAddlDescr.ItemId)
LEFT JOIN
(SELECT Sum([qty]-[invoicedqty]-[removeqty]) AS onhand, tblInQtyOnHand.ItemId
FROM tblInQtyOnHand
GROUP BY tblInQtyOnHand.ItemId) as [IN - On Hand Qty]
ON tblInItem.ItemId = [IN - On Hand Qty].ItemId
WHERE (((tblInItem.ItemStatus)=1) AND ((tblInItem.SalesCat)="PR") AND ((tblInItem.UsrFld1)="yes"))
ORDER BY tblInItem.ItemId;
The subquery is enclosed in brackets as part of the From clause and given a name that it can be referred to by in other clauses of the select query.
I have done a little bit of playing around with getting Access to "pass on" ODBC linked tables and had little joy. I did not do any extensive testing or searching, but what I did try was not pretty.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply