Linking to Access data

[Dale Fye]

In my Access 2003 application, I have a query that generates a large data set
and stores it in a temporary table. I also have an Excel spreadsheet that is
linked to this table. I am using Excel to view the data in a variety of
ways, and to format the font and background colors of the cells. I am also
using this format because I need to post this information to a portal so
others can download it.

My problem is this. Because of the formatting in the spreadsheet (column
headers, conditional formatting, ...) I cannot use the Access
Transferspreadsheet method to get my data into Excel. Because of this, I
have used Excel to link to the data table.

I've figured out how to open the Excel file (from Access), but cannot figure
out how to force it to refresh the link. The other problem is that when I
refresh the data manually in this spreadsheet, it changes the column widths
in the spreadsheet. I figure I can record a macro to reformat these columns
(how do I force an Excel macro to fire from within Access), but was wondering
whether there is a way to prevent this.

 

[Mike]

Why don't you try pulling the info into Excel
Sub getDataFromAccess()
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim i As Integer
Dim mydate1 As String
Dim mydate2 As String
mydate1 = Sheets(1).Range("F1")
mydate2 = Sheets(1).Range("F2")
i = 1

'Use for Access (jet)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Path\To\" _
& "MyDatabaseName.mdb;Persist Security Info=False"


strSQL1 = "SELECT FIELDNAME, FIELDNAME2 " _
& "FROM TABLENAME " _
& "WHERE (((TIME_STAMP) Between #" & mydate1 & "# " _
& "And #" & mydate2 & "#+1)) " _
& "ORDER BY FIELDNAME; "

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
Sheets("Sheet1").Range("A" & i) = rs1!FIELDNAME1
'Sheets("Sheet1").Range("B" & i) = rs1!FIELDNAME2
'Sheets("Sheet1").Range("C" & i) = rs1!FIELDNAME3
'Sheets("Sheet1").Range("D" & i) = rs1!FIELDNAME4
'Sheets("Sheet1").Range("E" & i) = rs1!FIELDNAME5
'Sheets("Sheet1").Range("F" & i) = rs1!FIELDNAME6
'Sheets("Sheet1").Range("G" & i) = rs1!FIELDNAME7
'Sheets("Sheet1").Range("H" & i) = rs1!FIELDNAME8
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close
End Sub