Excel 2003

[spooters]

Hi all,

I am having some problems with query's in Excel 2003 where refreshing the query it prompts for credentials to access the server but after entering it I lose the query and have to do it again. The problem doesnt always happen and have only starting having the issue with Excel 2003?

This is proving to be a big problem so any suggestions or help would be great..

Thanks

 

[Welshbird]

Spooters -is your Excel spreadsheet linking to an external data source then? What sort of external source?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[spooters]

Yes it is and that's the problem. The external data source. I can do that OK for new queries etc. which I can then save to use again but when I open these queries again the query is unable to establish the link again.

ODBC source

 

[Welshbird]

When you say unable to establish the link - do you get the ODBC box asking for a user name and password or what exactly happens?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[spooters]

I open an existing MS Query which I have saved.

I want to either refresh data or edit query.

Usually when you select either of these the query re connects to the SQL Server which is an ODBC source but in some cases the dialogue box appears asking for the password for the SQL Server. This already has the server name displayed.

This does not happen on all my queries but does on most

 

[SkipVought]

Is this a very complex query?

I have had this happen in instances where I an running a Transform/Pivot query.

Here's how I cover my posterior. In almost every case where I add a querytable that I will be refreshing, I macro record EDITING the query and then clean up the recorded coed and "refresh" the query by running the code, like...

Sub RefreshA010PROD()
    Dim sSQL As String
    
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  WP.WORKORDER"
    sSQL = sSQL & ", WP.PROGRAM"
    sSQL = sSQL & ", WP.CONSOLIDATED_PROGRAM"
    sSQL = sSQL & ", WP.LAST_UPDATE_DATE"
    sSQL = sSQL & ", WP.CMI"
    sSQL = sSQL & ", WP.SEQUENCE"
    sSQL = sSQL & ", WP.SAP_SHORT_TEXT"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM FPRPTSAR.WORKORDER_PROGRAMS WP"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE (WP.WORKORDER In (" & MakeList([WONO_274]) & "))"

    With wsWP.QueryTables(1)
        .Connection = _
        "ODBC;DSN=A010PROD;;DBQ=A010PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;" & _
        "LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[ADRIAN82]

Hi there. I'm not too sure wether im even in
the correct area to ask this question but ill put
it forward anyway.
My work has created a program on Access to store
many variable and set values.
We want to creat a data base or alternative to
the access program on excel as a backup program
to use if access DB crashes since we work on the
road.
I want any changes made in Access to automatically
transfer to the excel DB next time its opened.
So, they are linked lets say.
Values are
If that is toooo involved, the least we want to
be able to do is to table/chart the values from
the access Database. Above the chart id like to have certain details such as different customer and reference number for a large group of claim numbers.
The chart will split the claims as per our requirements.
Can anyone be so kind as to help me????
It may mean i end up impressing and keeping my job
although it has nothing to do with programming.
Any help is greatly appreciated.
Im in Sydney, Australia. So if there are any
Aussies, id even love to chat via telephone.
Thanks. ADRIAN82

 

[SkipVought]

ADRIAN82,

Please post your question in a NEW thread, in one of the many MS Access Forums.

Skip,
[sub]
[red][/red]
[/sub]