Access Query

[Guest]

Hi,

i have saved several queries in Access. Now i would like to import the data
in Excel with VBA function. Do you know how i can do that?

Sincerely

 

[Guest]

hi,
use microsoft query.
Data>get external data>new database query
if your access db is not listed, click the brouse button.
follow the wizard.
regards.

 

[Guest]

ok that's nice. Here the code i get :

Sub ImportWithAccessQuery()
'
' ImportWithAccessQuery Macro
' Macro enregistrée le 21/01/2005 par Bob
'
Dim DBPath As String
Dim MyRange As String, Query As String
DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb"
MyRange = "B16"
Query = "MyQuery"

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" & DBPath _
, _
";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _
, _
"OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range(MyRange))
.CommandType = xlCmdTable
.CommandText = Array(Query)
.Name = "financesoftbdd_1"
.SourceDataFile = DBPath
.Refresh BackgroundQuery:=False
End With
End Sub


I would like to get the results in something like a recordset, instead of
putting directly everything in a range, see : Destination:=Range(MyRange))
What are the variable that i can use in with Destination.

Thanx

 

[Guest]

hi,
myrange is B16. look at the top of the code under the dims.
this is microsoft query and it will dump the results of
the acess query myquery starting at range B16 from access
db financessoftbdd.mdb, table finacessoftbdd_1. and it
will not return a recordset.
do you understand this code?

-----Original Message-----
ok that's nice. Here the code i get :

Sub ImportWithAccessQuery()
'
' ImportWithAccessQuery Macro
' Macro enregistrée le 21/01/2005 par Bob
'
Dim DBPath As String
Dim MyRange As String, Query As String
DBPath = "J:\Documents and Settings\Bob\Mes documents\PFE\financesoftbdd.mdb"
MyRange = "B16"
Query = "MyQuery"

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=" & DBPath _
, _
";Mode=Share Deny Write;Extended

Properties="""";Jet OLEDB:System

 

[Guest]

This should help
http://www.ozgrid.com/forum/archive/index.php/t-25917.html

 

[Guest]

I understand it, i create a variable named MyRange, but i would like to know
if it's possible to use a recordset instead of that.