Access Query
 

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

Access Query
 

hi,
use microsoft query.
Dataget external datanew database query
if your access db is not listed, click the brouse button.
follow the wizard.
regards.

-----Original Message-----
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
.

Access Query
 

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 OLEDB:Databas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 " _
, _
"OLEDB:Don'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

Access Query
 

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;Passwo rd="
""";User
ID=Admin;Data Source=" & DBPath _
, _
";Mode=Share Deny Write;Extended
Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database 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 " _
, _
"OLEDB:Don'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

.

Access Query
 

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


"Laurent M" wrote:

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 OLEDB:Databas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database 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 " _
, _
"OLEDB:Don'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

Access Query
 

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.

" wrote:

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;Passwo rd="
""";User
ID=Admin;Data Source=" & DBPath _
, _
";Mode=Share Deny Write;Extended
Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databas" _
, _
"e Password="""";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database 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 " _
, _
"OLEDB:Don'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

.