Extracting from Access

[Guest]

Can anyone help with the code to perform the steps below?

Starting from an Excel file called TUV.xls...

1. Open an Access database called XYZ.mdb
2. Run a query called ABC
3. Copy the results to an Excel file called JKL.xls

Is this possible?
If you can help me, I won't bother anyone on this forum for at least an hour.

Thanks
Daniel

 

[Guest]

Hi,

I assume that TUV.xls is where you put the code.

As I use "SaveAs" to create the jkl.xls file, be aware that every copy of
the file contains the vba code.

HTH -- arunkhemlai

' --------------------------------------------
Option Explicit

Sub ImportFromAccess()

Dim dbname, dbpath, conn

' ** Here put your access filename.
dbname = "Northwind.mdb"

' ** Path to your access file.
dbpath = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\"

conn = _
"ODBC;" + _
"DSN=MS Access Database;" + _
"DBQ=" + dbpath + dbname + ";" + _
"DefaultDir=" + dbpath + ";" + _
"DriverId=25;" + _
"FIL=MS Access;"

With ActiveSheet.QueryTables.Add(Connection:=conn,
Destination:=Range("A1"))

' ** Change the query to what you want.
.CommandText = _
"SELECT Categories.CategoryID, Categories.Description " + _
"FROM `" + dbpath + dbname + "`.Categories Categories"

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Dim outname, outpath

' ** Output file name and path.
outname = "jkl.xls"
outpath = "c:\"

ActiveWorkbook.SaveAs Filename:=outpath + outname, FileFormat:=xlNormal

End Sub

' -------------------------------------

 

[Guest]

Thank you so much!
But can you tell me, the line:

.CommandText = _
"SELECT Categories.CategoryID, Categories.Description " + _
"FROM `" + dbpath + dbname + "`.Categories Categories"

I need to alter this, is that correct?
I'm not quite sure that I know how.

I altered the line below:
..Name = "Query from MS Access Database"

to

..Name = "LTM Coverage EMEA"

But it doesn't work (I get an error on ".Refresh BackgroundQuery:=False")

Can you help a little further - I'm not quite sure what to do.

Thanks!
Daniel

 

[Guest]

.CommandText = _

"SELECT Categories.CategoryID, Categories.Description " + _
"FROM `" + dbpath + dbname + "`.Categories Categories"

I need to alter this, is that correct?
I'm not quite sure that I know how.

The query string above was generated by the macro recorder,
that's why it looks so complicated.

A simpler version would be:
..CommandText = "SELECT CategoryID, Description FROM Categories";

So what you have to do is to simply replace the query string with your own.

I altered the line below:
.Name = "Query from MS Access Database"
to
.Name = "LTM Coverage EMEA"

No need, just leave it as it is.

But it doesn't work (I get an error on ".Refresh BackgroundQuery:=False")

Can't tell. It works fine for me.
Try commenting this line out and make sure that you copy the whole code.


Good luck -- arunkhemlai

 

[Guest]

Hi: Dan

The following post may also be of some help.

search: Connection using ADO (4/1/2005) this ng

good luck
TK