Create table from code - data refresh

[mbentley]

I am trying to select records from a table and export only the selected records to Excel.

There may be a better way to do this, but the only way I could figure out was to create a recordset from the original table, create a new table of just the selected records, export the new table as an Excel spreadsheet, and then delete the new table.

I have it all set up nicely, except for one small problem. Although the new table is getting the data I want, it is not appearing in the exported spreadsheet. Through extensive troubleshooting, I've discovered that the problem is that the data does not appear in the new table until after the code stops executing. (And yes, I have run the rs.Update command...the recordset won't close unless I do this.)

I'm sure there's some sort of recordset or database refresh or update command, but I can't find it. Anyone know what I need to do?

Funny thing is that the spreadsheet exports properly when I step through the code, but not if I let it run.

 

[WayneRyan]

Mike,

Just a hunch, put a DoEvents command after you close
the recordset and before you export.

hth,
Wayne

 

[mbentley]

Wayne,

Good idea! But it didn't work . I'm glad to know about that function though.

Thanks.

 

[WayneRyan]

Mike,

How about keeping the table and just deleting the records
before populating it:

sql = "Delete * from YourTable"
dbs.execute(sql)

It might be a good short-term workaround.

Wayne

 

[Jerry Stoner]

Unless Im missing something it may be easier to use a query. You really dont need a new table. Export the query instead.

Dim objExcel As Object
Dim strFile As String
Dim strFile2 As String
strFile = "C:\qcdatabase\chartbuffer.xls"
strFile2 = "C:\qcdatabase\scrapcharts.xls"

If DCount("*", "[qryScrapChart]") > 0 Then
DoCmd.OutputTo acOutputQuery, "qryScrapChart", acFormatXLS, strFile, False
Set objExcel = GetObject(strFile)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMinimized

Set objExcel = GetObject(strFile2)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMaximized
Else
MsgBox "There is no data to chart."
End If
ExitHere:
Exit Sub

This code exports a query and minimizes it and opens a second spreadsheet where I format the data and chart. I use 2 spreadsheets because the "buffer" workbook is overwritten each time the query is run. This way I can keep my formulas and chart format intact.

 

[mbentley]

Jerry,

Thanks for the idea. Normally, that's what I would do. I wasn't very clear in my post, but as far as I know I can't use a query to select the data. What I am doing is looking at a table and picking out records that have no entries in another table that is on the many side of the one-to-many relationship. This is a patient database, and the procedure removes patients who have been entered into the demographics table, but have had no subsequent entries in the admissions table - removing orphaned data, so to speak. I know of no way to do this with a query. Thanks for the idea, though. I agree with your point 100%.

 

[mbentley]

Wayne,

Thanks. That worked. I kind of figured I'd end up doing it that way. I was trying to keep the number of tables in the database to a minimum, but I guess there's no way around it.

Thanks again!