Import to excel Access database directly to a variable (not using sheets)

[internacio]

Hi all,

I would like to know if itīs possible to import data from access using directly a variable in VBA excel instead of pasting all values in my worksheet.

Thanks a lot.

[Patrick Molloy]

sure is -- help covers it.
In the IDE set a reference to the Microsoft ActiveX Data Obects Library
you probably have v2.7

Adapt the code below, which should be pasted into a standatd module.
The variable MyFile is assigned the Access Databas enaem that I'm using. You
should replace this with the full path etc of the Access database that you
want to use
You should also edit the SQL script accordingly...as my demo is pretty
simple, so is my sql
run the code and a connection is made to the database, the recordset in
memory gets populated according to the sql script. From the recordset fields,
the field names are dropped intio the sheet as table headers and finally the
data is copied directlyto the sheet



Option Explicit

Sub LoadDataFromAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQL As String
Dim i As Long

MyFile = "Risk.mdb"
SQL = "SELECT * FROM BondTable"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

rst.Open SQL, con, adOpenStatic

Cells.Clear

For i = 0 To rst.Fields.Count - 1
Cells(1, i + 1).Value = rst.Fields(i).Name
Next

Range("A2").CopyFromRecordset rst

rst.Close
con.Close

Set rst = Nothing
Set con = Nothing

End Sub

"internacio" wrote:

>
> Hi all,
>
> I would like to know if itÂīs possible to import data from access using
> directly a variable in VBA excel instead of pasting all values in my
> worksheet.
>
> Thanks a lot.
>
>
> --
> internacio
> ------------------------------------------------------------------------
> internacio's Profile: http://www.excelforum.com/member.php...o&userid=30960
> View this thread: http://www.excelforum.com/showthread...hreadid=506308
>
>

[Tom Ogilvy]

In Patrick's code, he is writing the information to the worksheet. You
could put it in an array with something like

Dim v as Variant

v = Application.Transpose(rst)

or work with rst directly.

--
Regards,
Tom Ogilvy

"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> sure is -- help covers it.
> In the IDE set a reference to the Microsoft ActiveX Data Obects Library
> you probably have v2.7
>
> Adapt the code below, which should be pasted into a standatd module.
> The variable MyFile is assigned the Access Databas enaem that I'm using.
You
> should replace this with the full path etc of the Access database that you
> want to use
> You should also edit the SQL script accordingly...as my demo is pretty
> simple, so is my sql
> run the code and a connection is made to the database, the recordset in
> memory gets populated according to the sql script. From the recordset
fields,
> the field names are dropped intio the sheet as table headers and finally
the
> data is copied directlyto the sheet
>
>
>
> Option Explicit
>
> Sub LoadDataFromAccess()
> Dim MyFile As String
> Dim con As New ADODB.Connection
> Dim rst As New ADODB.Recordset
> Dim SQL As String
> Dim i As Long
>
> MyFile = "Risk.mdb"
> SQL = "SELECT * FROM BondTable"
>
> con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile
>
> rst.Open SQL, con, adOpenStatic
>
> Cells.Clear
>
> For i = 0 To rst.Fields.Count - 1
> Cells(1, i + 1).Value = rst.Fields(i).Name
> Next
>
> Range("A2").CopyFromRecordset rst
>
> rst.Close
> con.Close
>
> Set rst = Nothing
> Set con = Nothing
>
> End Sub
>
> "internacio" wrote:
>
> >
> > Hi all,
> >
> > I would like to know if itīs possible to import data from access using
> > directly a variable in VBA excel instead of pasting all values in my
> > worksheet.
> >
> > Thanks a lot.
> >
> >
> > --
> > internacio
> > ------------------------------------------------------------------------
> > internacio's Profile:
http://www.excelforum.com/member.php...o&userid=30960
> > View this thread:
http://www.excelforum.com/showthread...hreadid=506308
> >
> >