Access Lagging behind SQL Server

[Echidna]

I am trying to export a query held within a table as T-SQL out to excel.
The idea is to have admins write the Queries, which will be exported by other personnel.

The View and Table do create correctly as I can see them within ssms, however I get a runtime error 7874 - cannot find object.

The only thing i can think of is that access is lagging behind sql server, and the database window is not being refreshed.

This is an ADP file running on Access 2007 on a SQL Server 2008 Standard Server.

DoCmd.SetWarnings Warningsoff
Dim strsql As String
Dim strsql2 As String
Dim strexport As String
Dim strexcel As String
Dim str As String
 
'set the name for the view and the exported file
strsql2 = Me.txtEXPName & " - " & ENVIRON("USERNAME") & DatePart("d", Date) & DatePart("m", Date) & DatePart("yyyy", Date)
strsql = Me.txtEXPName & ENVIRON("USERNAME")
strexport = Me.txtEXPName & " - " & DatePart("d", Date) & "-" & DatePart("m", Date) & "-" & DatePart("yyyy", Date)
strexcel = "C:\Business Support\Exports\" & Me.txtEXPName & " - " & DatePart("d", Date) & "-" & DatePart("m", Date) & "-" & DatePart("yyyy", Date) & ".xls"
 
'determine if the Folder exists
If FolderExists("C:\Business Support\Exports") = False Then
MkDir "C:\Business Support\Exports"
Else
End If
 
'determine if the output file already exists
If FileExists(strexcel) = True Then
MsgBox "The Output File " & strexcel & vbCrLf & "Already exists" & vbCrLf & vbCrLf & "Please rename or delete the file before proceeding", vbCritical, "File exists"
Exit Sub
Else
End If
 
'If the view exists on the server, delete it
CurrentProject.Connection.Execute "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[" & strsql & "]')) DROP VIEW [dbo].[" & strsql & "]"
CurrentProject.Connection.Execute "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & strsql2 & "]')) DROP TABLE [dbo].[" & strsql2 & "]"
 
'Create view on the server
CurrentProject.Connection.Execute "CREATE VIEW " & strsql & " AS " & Me.txtExport & ""
 
CurrentProject.Connection.Execute "SELECT * INTO " & strsql2 & " FROM " & strsql & ""
 
CurrentProject.Application.RefreshDatabaseWindow
 
'Transfer the data to MS 2003 Compatible Excel File
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strsql2, strexcel, True
 
'delete the view from the server
CurrentProject.Connection.Execute "DROP VIEW " & strsql & ""
CurrentProject.Connection.Execute "DROP Table " & strsql2 & ""
CurrentProject.Application.RefreshDatabaseWindow
DoCmd.SetWarnings Warningson
 
MsgBox "Export Complete" & vbCrLf & vbCrLf & "File is located at the following location" & vbCrLf & strexcel & vbCrLf & vbCrLf & "Press OK to open the file", vbOKOnly, "Export Completed"
 
'open the excel application
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    With xlApp
       Set xlWB = .Workbooks.Open(strexcel, , False)
    .Visible = True
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Save
    End With
 

[ADezii]

1. It's probably a dumb question, but I must ask it, do you have a Reference set to the Microsoft Excel Object Library?
2. Have you tried to set a Breakpoint then Single-Step through the code to see exactly where the Error is being generated?

[Echidna]

Hiya,

not such thing as a dumb question :)

I have the excel library referenced.

I have set a breakpoint, and the error is coming up (occasionally) at the docmd.transferspreadsheet line.

If i run the script again (after the error), the spreadsheet is produced.

Cheers and Thanks

Leon

[Stewart Ross]

Hi. I'm sure this is nothing to do with Access as such; it is the Excel automation that is causing the problem. Reason is that you are referring implicitly in many locations to the current active worksheet object (lines 56 through 81). This works when running code in Excel, as the currently-active worksheet or workbook is taken for the implicit references. It does not work reliably when running code referencing the Excel object from outside of Excel itself.

You MUST refer explicitly to the automation server object whenever you want to set or refer to a range or selection. You have a WITH in line 53 which will allow you to do this without referring to xlApp all the time, but you then do not use the properties of that object when referring to selections, rows etc (lines 56 onwards).

For example, lines 56 to 61 can be replaced as follows:

with .activesheet.Rows("1:1").Font 
        .Bold = True 
        .ThemeColor = xlThemeColorDark1 
        .TintAndShade = -0.249977111117893 
End With 
 

(In this example I have also done away with the use of the Selection method - its use slows down code running in an automation server).

To summarise, you must refer to the xlApp object explicitly throughout. Use With to help you in this, but when you do make sure you use the correct properties of the xlApp object - its ActiveWorkbook, ActiveSheet, Workbooks() etc properties - whenever you access ranges or range properties.

There are a number of threads dealing with the errors which arise when trying to use implicit references. This thread linked here gives more background on the reason for what can be very frustrating errors experienced with code running in Access that apparently works OK in Excel itself.

-Stewart

[ADezii]

Really nice pickup, Stewart!

[Stewart Ross]

Thanks for your comment, ADezii!

Echidna, I should also have mentioned that you have not explicitly saved the file and closed Excel at the end of your routine. This may be why you are experiencing an error on the TransferSpreadsheet method rather than on the Excel components (which I can assure you will happen at some time even if you haven't experienced it so far!).

Unless you close Excel explicitly, on each occasion you run the routine you will create a new instance of Excel which will remain active until you physically close it, along with the file you opened. Should the name of the file still open be the same for whatever reason as the one you are passing to TransferSpreadsheet you will get a transfer failure, as you cannot save a file under a name in use by another application at the same time.

-Stewart

[NeoPa]

Some useful basics and some sample code for Application Automation.

[Echidna]

Many Thanks guys :) will give it a go this morning :)

Did not think of the excel automation causing an issue...
I recorded a macro and copied the VB straight into Access.

Cheers

Leon

[Echidna]

I have changed the code within the excel formatting, but the issue - error 7874 is still there.

I commented out the excel formatting, and the problem still exists.
It seems that the table exists on the server, however, there seems to be a delay in the Database window being populated with the Table created.

Cheers

Leon

[Stewart Ross]

Presuming you are using an ODBC connection to your SQL-server back end it would be worth reducing the ODBC refresh interval for the database. Although the Help entry for this setting is concerned with data entry form refreshing when records are changed by other users in a multi-user environment, I reckon it could be that your database needs to refresh its ODBC status at more frequent intervals. At least it will eliminate another potential problem from consideration.

-Stewart

[Echidna]

Hi, sorry for not responding in a while, I have been given another priority one project for the past couple of weeks.

I set the odbc refresh interval to one second and it works a charm now... many thanks :)

Cheers

Leon

[NeoPa]

Leon,

I'm sure you have the right solution there. However, I'd be careful about using a value as short as one second. This could be creating quite a lot of traffic on your networks. I'd advise playing with values nearer to your original setting until you find it works reliably for you.

[Echidna]

Thanks for the tip :)

the advantage of having a standalone testing environment :)

It was set originally to 5 seconds (odbc default), i am thinking of taking the original idea of 'holding' the application for 5 seconds to allow the odbc to refresh itself, but everything i try does not work (short of killing the connection and re-establishing the link).

Is there some way within an adp of determining if the created Table exists?

Cheers

Leon

[NeoPa]

@Echidna
I don't do any work in ADPs myself, but would you not have the TableDefs collection available. That can tell you all the tables available within the CurrentDb.

[Echidna]

Hiya,

Got it to work... finally :)

One of the VFP developers suggested a "try catch", which got me thinking :)

Here is what i came up with

CurrentProject.Application.RefreshDatabaseWindow
 
    Me.txtdisplay = "Processing..."
    Screen.MousePointer = 11
        gcdate = Now()
        Do While Now() < gcdate + 0.00003
            DoEvents
        Loop
    Screen.MousePointer = 1
    Me.txtdisplay = ""
 

It gives the application time to refresh the window without the need to reduce the odbc connection attributes.

Cheers, and thanks for all the help!!

Leon

[NeoPa]

Nice work Leon.

An alternative approach (only whole seconds supported mind) would be to use :

Do While Now() < GCDate + #0:0:3#