Prev: The file on device 'tape' is not a valid Microsoft Tape Format backup set.
Next: SQLSERVERAGENT - Alert Engine - 322
From: Ade on 2 Feb 2006 10:30
Sorry my mistake, it does work. You have to replace the string "Microsoft®
Query" with something else.
--
Ade


"Ade" wrote:

> I have the exact same problem using Excel 97/MS-Query8. I tried your
> suggestion, it did not work. I am still getting the error.
> --
> Ade
>
>
> "preddy" wrote:
>
> > I had the same problem, but there are multiple users who use that
> > spreadsheet using Windows authentication. I did not want any of them to
> > be sysadmins. They only had SLECT permissions to certain tables. In my
> > case, deleting the Application name (from the SQL Server Login dialog)
> > fixed it. You need to click the Options button on the dialog to see
> > this. The application name seems to be an issue if it has certain
> > characters (like ® in Microsoft® Query). Depending on the MS Office
> > version, the default value may be Microsoft® Query/MS Office XP/??.
> >
> > You can also programmatically change the connection string (and even
> > the command text if necessary). Here is a sample.
> >
> > 'ChangeConnection
> > Sub ChangeConnection()
> >
> > Dim sh As Worksheet
> > Dim qt As QueryTable
> >
> > Dim sConnection As String
> >
> > For Each sh In ActiveWorkbook.Sheets
> > For Each qt In sh.QueryTables
> >
> > 'Show current connectionstring
> > MsgBox ("Tab: " & sh.Name & vbCr & " Current Connection: "
> > & vbCr & qt.Connection)
> > 'Show current query
> > MsgBox ("Tab: " & sh.Name & vbCr & "Current Query: " & vbCr
> > & qt.CommandText)
> >
> > 'Change Connection
> > qt.Connection = "ODBC;DRIVER=SQL
> > Server;SERVER=myserver;DATABASE=myDB;Trusted_Connection=Yes;APP=Excel_TopCustomers;"
> >
> > 'Change Qry text (the owner for instance)
> > qt.CommandText = Replace(qt.CommandText, "DB.dbo.",
> > "DB.Me")
> >
> > qt.SavePassword = False
> >
> > 'Show new connectionstring
> > MsgBox ("Tab: " & sh.Name & vbCr & "Connection: " & vbCr &
> > qt.Connection)
> > 'Show new query
> > MsgBox ("Tab: " & sh.Name & vbCr & "Query: " & vbCr &
> > qt.CommandText)
> >
> > Next qt
> > Next sh
> >
> > End Sub
> >
> >
First  |  Prev  | 
Pages: 1 2
Prev: The file on device 'tape' is not a valid Microsoft Tape Format backup set.
Next: SQLSERVERAGENT - Alert Engine - 322