ACCESS QUERY INTO EXCEL

[JonRowland]

Hi, can anyone help?

I have a query in Access which users need to analyze in Excel. That bit is simple.

When sending the query to Excel only the first 255 characters are taken. Is there any way more that 255 characters can be sent to Excel?

Thanks
Jon

 

[Ziggy]

What versions are you using and what method to export to Excel?

 

[JonRowland]

Hi,

Am using 97 versions. I have now found out how to do this by using the TransferSpreadsheet method.

Jon

 

[lord of the badgers]

an alternative is to look at the 'Get External Data' routine in Excel itself - it's on the Data Menu, Create Database Query...

I use this far more than any Transfer Spreadsheet/OutputTo commands - tho this may not answer your 255 chars question I might add!

you can do all sorts with it... set up criteria, get formulae in excel to fill down alongside the data (very useful), beauty is it'll refresh from an Access source even if it's in a read only (to the users) portion of your shared drives.

you can set it to refresh as you open - or not at all, and you can also build statements in VBA to pass SQL to the database.

I have a spreadsheet that has two such tables on one sheet, you double click on the one table (the menu) and the other table populates with the data related to the item you clicked.. and it's fast too.

You can use Styles to autoformat the data should you want to.

you can also drive pivot tables this way

Have a look in the help pages...

Of course you may know this, in which case i'll get me coat..

 

[Swamp Thing]

Re: 'Get External Data' tips

L. of the B.,

Lots of useful tips in your post!

Thought I'd post a spin-off question here.

I have a query in Access that is set up to accept a parameter for a filtering criteria. Can I have an Excel query take the parameter value from a cell, and use this to supply the criteria to the Access query?

Regards,
S.T.

 

[bat17]

I dont think that you can pass the Criteria from Excel to Access when using MSQuery to get the data, but what you should be able to do is to recreate the Access query in MSQuery and add the criteria there.

HTH

Peter

 

[lord of the badgers]

Sorry Bat17, but oh yes you can. this is what i referred to in previous post.
remember, when you set up a query using the wizard, you have the opportunity to add criteria? this routine uses that...

'################################################
Sub refresh_it()
Application.ScreenUpdating = False

'obtain the value that is in your workbook
dbase = Range("g1").Value
'set up the string to pass to MSQuery or whatever it is!

sql_connect = Array("SELECT `Table Query`.`Short description`, `Table Query`.Table, `Table Query`.Field, `Table Query`.Description" & Chr(13) & "" & Chr(10) & "FROM `C:\MyFolder\MyDatabase`.`Table Query` `Table Query`" & Chr(13) & "" & Chr(10) & "WHERE (`Table Query`.Table='" & dbase & "')")

'select the data query area
Range("b2").Select
'with that, run the refresh
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DBQ=C:\MyFolder\MyDatabase.mdb;DefaultDir=C:\Myfolder;Driver" _
), Array( _
"={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;MaxBufferSize=512;MaxScanRows=8;PageTimeout" _
), Array("=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"))
.Sql = sql_connect
.Refresh False
End With
Range("g1").Select 'er, if you want to that is
Application.ScreenUpdating = True
End Sub
'#########################

notice how i concatenated the value "dbase" (i'm rubbish at keeping to naming conventions!), and splice it into the SQL statement.


this is called from a double click event on my particular spreadsheet.. a button or whatever would do as well.
------------------------------------------------------------------------
the tip here, is to use my code as inspiration, not in actuality ... start by creating a data query in excel. then, record a macro, and during this, click on the query area, right click, refresh. Stop recording and you SHOULD have all the connection bullsh1t ready written for you.
------------------------------------------------------------------------

mail or PM me if you need more specific help

 

[bat17]

I think that you are doing what I said and recreating the query in an Excel SQL. I dont think that this will work when connecting to a query in Acces that is expecting to get its parameter info from a Form or custom function.

Peter

 

[lord of the badgers]

ah ok. i thought you were meaning that the user was to fire up Query directly and edit it there. I AM doing it in the way you said then(!), but your post inferred to me otherwise (well the way i read it).

in any case you are right in saying you can't (or rather don't need to) go into Access to do this.

soz about that!

either way, hope all this malarkey helps

for the record, the above method works specifically with Tables, Select Queries and Crosstab Queries (i think). If you can't access it you won't see it in the table list anyway.

 

[Swamp Thing]

Re: Sending params from Excel to Access

Thanks for your comments..

The reason I posted the question was, that I once had a complex query that worked well in Access, but MS-Query could not handle it in the normal way. ("Can not display graphically") . And that is precisely the kind of query for which MS-Query does not accept parameters!

I do not remember the details, but you have probably seen examples of complex (nested?) queries that MS-Query has a problem with.

Is there a work around for this situation?
Regards,
S.T.