Access from Excel

[JimS]

I have an extensive Access database that folks are always wanting to export
to excel. I've set up a table of query names/descriptions for export queries
I've developed. Users log in, open a form (using switchboard), and select a
query description they want, point to an excel spreadsheet where they want it
exported, then click a button, it gets exported using the access
transferspreadsheet method.

I'd like to reverse the process. I'd prefer to keep people out of my db and
have 'em "suck" those query results into a spreadsheet instead. I'm sure I
can do this, but I need some structural advice. I'm comfortable with vba,
though not a whole lot of work in excel vba (Access, and believe it or
not...Project vba.)

So, presumably, I'd create some selection table by sucking in the already
existing table from Access, then use that to select the query the user wants,
then pull that query into a sheet by itself.

Strategies?

 

[FSt1]

hi,
you could use Microsof query. no vb required or not a lot.
on the menu bar..
data>get external data>new database query
database type = microsoft access
brouse to your data base.
follow the wizard.
on the last screen of the wizzard are options.
1. return data to xl take you and the data back to xl
2 view data in MSQ take you into microsoft query. you will find it
remarkably similar to access.
if you already have the querys written in access, it would be better to suck
the query results into xl instead of linking to the access tables and
rewriting the query in microsoft query(MSQ). if you don't have the querys
written in access, it would be better to write them in access instead of MSQ
becasue access is a tad bit more sophistacated than MSQ.
you can refresh the MSQ with worksheet open event.
Private Sub Workbook_Open()
Sheets("sheet1").Range("A1"). _
QueryTable.Refresh BackgroundQuery:=False
End Sub
or as my users perfered, from a command button on the sheet with the above
code.
that way they could refresh as frequently as they wanted ie refresh, do some
work then refresh again to see if anything changed on them.
in excel you can put formulas on the sides and at the bottom of the MSQ
range but you cannot add rows or columns to the MSQ range. if you do, xl will
throws up error messages at refresh.
xl views the MSQ range as a named range and will look something like.
Query_from_MSAccess_Database sheet1 or something.
your users(like mine) will want to archive certain (or all) query runs. if
they do a file saveas, they will be saving the file, the query,
button....all. and the archive will be refreshable. so i added a second
button..save query.
Sub macSaveRange()
ActiveSheet.UsedRange.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
ActiveWorkbook.Names("name").Delete
Application.Dialogs(xlDialogSaveAs).Show
End Sub
copies the query, create a new workbook, pastes the data, deletes the query
in the new workbook then show the file saveas dialog.
I was managing 30+ MSQ's at one time. i created an xl file to list them.
file name, access query or table use and other stuff.

try it. you'll like it.

regards
FSt1