October 25, 2006 at 10:30 am
You can assume that I'm not good with connectivity issues and don't have resources at my company.
We're using SQL Server 2000, MS Access 2003 and Excel 2003.
I use MS Access to extract data from our various tools and create reporting, putting it into Excel spreadsheets for easy access by people in our company.
I have several tables and queries that reside in one of my Access databases, and I wanted to join some of this data with the SQL Server tables to create queries which I could then access through MS Excel.
I have the queries all ready, but when I try to create a pivot table in Excel by connecting to the MS Access database and selecting the query that contains both Access tables and SQL Server tables, I get:
ODBC-connection to 'SQL Server xxx.xxx.x.x" failed.
I've been looking through documentation and the web to see how to work around this and can't find anything to help. I'm assuming it's because I need a password to log into SQL Server and the password isn't stored anywhere.
I tried a pass-through query and got a different error:
Reserved Error [|]; there is no message for this error.
Is there a way to have the connection store the password so that I can use the query, or is there a different problem here?
If the solution is something I need to read in a book, please point me in the right direction to find the answer.
Please help! I'm not the DBA (we don't have one), so I can't just re-create all my data on SQL Server.
Leslie
October 26, 2006 at 12:31 am
I found this but it does not work all the time
I think it needs some correction with the ODBC connection in Windows
'//Name : CreateDSNConnection
'//Purpose : Create a DSN to link tables to SQL Server
'//Parameters
'// stServer: Name of SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function CreateDSNConnection(stDescription As String, stServer As String, stDatabase As String, _
Optional stUsername As String, Optional stPassword As String) As Boolean
On Error GoTo CreateDSNConnection_Err
Dim stConnect As String
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "Description=" & stDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
Else
stConnect = "Description=" & stDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr _
& ";UID=" & stUsername & vbCr & ";PWD=" & stPassword & ";" & vbCr
End If
DBEngine.RegisterDatabase stDescription, "SQL Server", True, stConnect
'// Add error checking.
CreateDSNConnection = True
Exit Function
CreateDSNConnection_Err:
CreateDSNConnection = False
MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description
End Function
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply