Excell error with Linking Database (via ODBC) "run-time error '1404': Method 'Refreshall' of object'_workbook' failed

Hi Experts,

the person that made this excel document is the only one who can refresh the data, all other users receive this error.


desc

Excell throws this error when attempting to refresh a page that uses an odbc connection
 "run-time error '1404': Method 'Refreshall' of object'_workbook' failed


he is using the sql native client 10 odbc driver, while the users are using the built in sql odbc driver 6.x sqlsrv32





I
JamesAnctilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JamesAnctilAuthor Commented:
let me correct my self. Not all other users.... all other machines. I have attempted to refresh the data logged in as that user on a different machine.
0
SteveCommented:
Could you post a copy of the connection string used.
A copy of the workbook would be most handy, but the connection string should be enough.
0
JamesAnctilAuthor Commented:
hmmm, how can I post a copy of the connection string?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SteveCommented:
Depends how the data gets to the sheet...

If the connection is through VBA then alt+F11 will open up the editor, the connection string will be in there somewhere.
If the connection is via query table, go to data connections properties and the connection string is in there.
Or post the workbook and we can find it :-)
0
Jacques Bourgeois (James Burger)PresidentCommented:
The 6.x drivers dates from the end of the 90's and are too old to use with SQL Server 2008. You will need to update the machines with newer drivers.

I do not know if version 10 is still available, I cannot find it on the Microsoft site, but version 11 is at http://www.microsoft.com/en-us/download/details.aspx?id=36434 and is compatible with SQL Server 2008.
0
JamesAnctilAuthor Commented:
Here is the workbook ;-)

I installed teh 11 driver on a workstation and created an odbc connection to the sqlserver, but i get same error.
0
JamesAnctilAuthor Commented:
woops attached
Sage-Sku-Parser.xlsm
0
Jacques Bourgeois (James Burger)PresidentCommented:
The problem is not in the code, it is definitively something that has to do with the connection and the rights of the users. Unfortunately, Excel gets the real error code and throws it to you with a useless message.

You might give a look at the log event on the computers where you have the problem. ODBC drivers often log their errors in there, so you could understand why the request is rejected by the server, which seems to be the situation.
 
Is the DSN created with exactly the same parameters?

Do the other users have the same permissions in Sage?

Since the machines on which you had problems had quite old drivers, is it possible that they are old computers that do not have enough resources. During a Refresh, it is quite possible that there are 2 sets of data in memory. The one already in Excel and the one that is being created. If the database is large, that might have an impact.
0
SteveCommented:
The data the user is connecting to is an access database (2010 format)..

U:\DataBase Files\SageSkuParser.accdb

The connection string is using JET directly... so no machine DSN (as you would expect using ODBC with excel)

The query in the database the data comes from is:  qry_Backbone_01

Can you check the access database on U:\DataBase Files\SageSkuParser.accdb
Can all users access this?
Can the users run the query in the access database... the error which excel is not forwarding will appear in Access when you try to run the query.
If they can the security settings will pop up on first use of the Access database... set to ok and always trust. This may sort the issue.

You may find it better to change the table creation method... but test the above first, then can run through the use of MS Query to populate a Table.
0
Jacques Bourgeois (James Burger)PresidentCommented:
Good find The_Barman. I did not think to look because the original message has SQL Server topics and spoke of ODBC connection through the SQL server drivers.

But one should also look at the real source of the data is. Many users work in Access with linked tables that are really in SQL Server. If this is the case the ODBC driver and DNS are possibly still the source of the problem.

I haven't work with Access for a while, but in my times, ODBC tables where identified by an earth icon instead of the grid icon used for native Access tables.
0
SteveCommented:
Indeed, there is most likely a connection to an old AS400 or SQL server which is set using a DSN which only the one user has. This will become apparent when looking at the query through Access rather than Excel.

I hate using Access as an intermediate tool.
It should be possible to use Excel (in particular MS Query in Excel) to do the whole thing.

If we first find the problem, likely Access end rather than Excel, we can discuss how to populate the table better.
0
JamesAnctilAuthor Commented:
Ok so I attempt to run the query  in the access database...

receive error

"odbc--connection to 'SQL server native client 10.0sqlserver' failed.
0
SteveCommented:
OK, so now we need to check the connection string for the table in the access database.

Do the linked tables in Access have full connection strings or do they use a System DSN?

If you hover over a table with a 'globe' icon does it say DSN or something else?
0
JamesAnctilAuthor Commented:
here is a screen.

Looks like it is trying to use that sql native dobc driver 10.x?

access
0
JamesAnctilAuthor Commented:
Thoughts?
0
SteveCommented:
Well, the connection string is unfamiliar to me,  there is no address in there and the server is down as just SQLSERVER, which to me looks like it may be pointed at a machine reference.
I will be able to look at this at work, or someone else may have an answer before then.
0
JamesAnctilAuthor Commented:
hmmm

the database server is  "sqlserver"  


does the driver its using have anything to do with this?
0
SteveCommented:
OK... could you try the following:

1) Rename table 'dbo.AR_InvoiceHistoryDetail' to 'dbo.AR_InvoiceHistoryDetail_1'

2) in the access database press [ALT]+[F11]

3) Insert > Module

4) Paste the following code into the new module:
Option Compare Database
Sub CreateLinkedTable()
Dim xx As Variant

xx = AttachDSNLessTable( _
"dbo.AR_InvoiceHistoryDetail", _
"dbo.AR_InvoiceHistoryDetail", _
"SQLSERVER", _
"MAS_DBS", _
"", _
"")

End Sub

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Open in new window


5) Click inside the macro 'CreateLinkedTable' and click run icon at top of screen.
    This should hopefully not error out.

6) If no error occurred, close the database and re-open.

7) the Table should now be visible and should connect.

It should create the same table as before but with a diferent method.
This process may need to be  done for all tables.
But for now, test just this table and let us know if it errors out.
0
JamesAnctilAuthor Commented:
Before I have my guy change his code.

What is different?

How would this code change allow the other machines run the query?


Remember, the query works on the creators computer.

do we know why this query doesn't work on other machines? have we ruled out the fact that the creator is using a different odbc driver than all others (sql native client 10)?
0
SteveCommented:
What this would do is to connect to the SQL server in a different way (one which all users should have no issue with).
The linked tables will be all that changes (once).
So there would be no difference to the users after changing the table connection method.
All the tables and therefore query will function exactly as before.

There is no guarantee that this will fix the problem (may not work on your structure).
But if it does it will get you going again with the bare minimum of fuss.
ie no changes to anything else after a one off change to each table.

If it does not work... simply re-name the old table back to what it was before and no harm done.
So there is all to gain, and nothing to loose as it takes a minute to change back.
0
JamesAnctilAuthor Commented:
Ok I have sent that to the sql guy.


So if we are connecting to to the sql server in a different way, this leads me to believe that the issues lies within the odbc connection?
0
SteveCommented:
Have you tested the code and the change?
you may be wasting your SQL guys time if the code doesn't work :(
So it is a simple thing to test.
If it does work, we can describe the change and the implications.
Can also provide code to create tables in Excel... dropping the need for the access database.
0
JamesAnctilAuthor Commented:
i am having him test the code change.

I am waiting for him to respond.
0
JamesAnctilAuthor Commented:
ooo wait are you saying make that change in the excel document?
0
SteveCommented:
No, in the access database... To the source table... As a one off change... Will cascade down from there without need to change the excel file.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.