Importing from Access to Excel

  • Thread starter Thread starter faddrick
  • Start date Start date
F

faddrick

Hello,
I have the following doubts ,
1) how do i transfer the data's from Access to Excel based
on users condition using Vb as front end?
2) How do i display the fields to the end user from a ms
access query in vb ?

plzz reply me asap.

Faddrick
 
Hi Faddrick,

1) If you're using Visual Basic or Excel VBA (as opposed to Access VBA)
to work with data in an mdb file,
a) build a SQL SELECT statement incorporating the user's conditions
b) open a recordset on this
c) use Excel's Range.CopyFromRecordset method to put the data into
your worksheet.

If you're using Access VBA, you could do the same, or else (with less
flexibility and some security issues in Access 2003):
a) build the SELECT statement
b) assign it to the SQL property of a query (QueryDef)
c) use the query with DoCmd.TransferSpreadsheet to export the data.

2) Assuming you're using Visual Basic: if you want the names of the
fields, use something along the lines of this air code. If you want to
display the data, study the VB help on recordsets, data controls and (I
think) the DataGrid control. If you still can't get started, ask in a
Visual Basic group rather than an Access one.

Dim odbE As DAO.DBEngine
Dim odbD As DAO.Database
Dim F as DAO.Field

Set odbE = New DAO.DBEngine
Set odbD = dbE.OpenDatabase("D:\folder\file.mdb")

For Each F in odbD.QueryDefs("MyQuery").Fields
Debug.Print F.Name
'put them into a listbox perhaps
Next

odbD.Close
Set odbD = Nothing
Set odbE = Nothing
 
Thanks for the information but still i couldnt get the
expected out put.
so if u have any other ideas can u just pass it to me.

tq
bye
faddrick
-----Original Message-----
Hi Faddrick,

1) If you're using Visual Basic or Excel VBA (as opposed to Access VBA)
to work with data in an mdb file,
a) build a SQL SELECT statement incorporating the user's conditions
b) open a recordset on this
c) use Excel's Range.CopyFromRecordset method to put the data into
your worksheet.

If you're using Access VBA, you could do the same, or else (with less
flexibility and some security issues in Access 2003):
a) build the SELECT statement
b) assign it to the SQL property of a query (QueryDef)
c) use the query with DoCmd.TransferSpreadsheet to export the data.

2) Assuming you're using Visual Basic: if you want the names of the
fields, use something along the lines of this air code. If you want to
display the data, study the VB help on recordsets, data controls and (I
think) the DataGrid control. If you still can't get started, ask in a
Visual Basic group rather than an Access one.

Dim odbE As DAO.DBEngine
Dim odbD As DAO.Database
Dim F as DAO.Field

Set odbE = New DAO.DBEngine
Set odbD = dbE.OpenDatabase("D:\folder\file.mdb")

For Each F in odbD.QueryDefs("MyQuery").Fields
Debug.Print F.Name
'put them into a listbox perhaps
Next

odbD.Close
Set odbD = Nothing
Set odbE = Nothing






Hello,
I have the following doubts ,
1) how do i transfer the data's from Access to Excel based
on users condition using Vb as front end?
2) How do i display the fields to the end user from a ms
access query in vb ?

plzz reply me asap.

Faddrick
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Not without more information on exactly what you're trying to achieve,
whether you're working in VB, Excel or Access, what you've tried and how
it doesn't meet your expectations.

Thanks for the information but still i couldnt get the
expected out put.
so if u have any other ideas can u just pass it to me.

tq
bye
faddrick
-----Original Message-----
Hi Faddrick,

1) If you're using Visual Basic or Excel VBA (as opposed to Access VBA)
to work with data in an mdb file,
a) build a SQL SELECT statement incorporating the user's conditions
b) open a recordset on this
c) use Excel's Range.CopyFromRecordset method to put the data into
your worksheet.

If you're using Access VBA, you could do the same, or else (with less
flexibility and some security issues in Access 2003):
a) build the SELECT statement
b) assign it to the SQL property of a query (QueryDef)
c) use the query with DoCmd.TransferSpreadsheet to export the data.

2) Assuming you're using Visual Basic: if you want the names of the
fields, use something along the lines of this air code. If you want to
display the data, study the VB help on recordsets, data controls and (I
think) the DataGrid control. If you still can't get started, ask in a
Visual Basic group rather than an Access one.

Dim odbE As DAO.DBEngine
Dim odbD As DAO.Database
Dim F as DAO.Field

Set odbE = New DAO.DBEngine
Set odbD = dbE.OpenDatabase("D:\folder\file.mdb")

For Each F in odbD.QueryDefs("MyQuery").Fields
Debug.Print F.Name
'put them into a listbox perhaps
Next

odbD.Close
Set odbD = Nothing
Set odbE = Nothing






Hello,
I have the following doubts ,
1) how do i transfer the data's from Access to Excel based
on users condition using Vb as front end?
2) How do i display the fields to the end user from a ms
access query in vb ?

plzz reply me asap.

Faddrick
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
I tried your code in vb but i couldnt get the expected
output.


Let me explain to u what iam looking for .
i have two tables table a and table b

table a has the fields empno,empname,sex,age.

table b has the fields experience,salary,qualification.

i created a query in msaccess named empquery.

naturally it will display the selected fields.

now i want to call empquery in visual basic.

for that i have created a button.
when user clicks this button the query name,empquery,
should appear in
a combobox and also all the fields in the
query,empquery,must be
displayed in a list box ,named available fields.

i have one more list box named selected fields.
user selects the required fields ,to be displayed in
excel,from
available fields to selected fields.

i have a button which takes user to an another form where
the user
can give some condition , for example empno >= 1000
i wanted to transfer all the datas where empno greater
than or equal
to 1000 to excel.

i hope my problem is very clear to u now.

pl help me.








-----Original Message-----
Not without more information on exactly what you're trying to achieve,
whether you're working in VB, Excel or Access, what you've tried and how
it doesn't meet your expectations.

Thanks for the information but still i couldnt get the
expected out put.
so if u have any other ideas can u just pass it to me.

tq
bye
faddrick
-----Original Message-----
Hi Faddrick,

1) If you're using Visual Basic or Excel VBA (as
Click to expand...
opposed
to Access VBA)
to work with data in an mdb file,
a) build a SQL SELECT statement incorporating the user's conditions
b) open a recordset on this
c) use Excel's Range.CopyFromRecordset method to put the data into
your worksheet.

If you're using Access VBA, you could do the same, or else (with less
flexibility and some security issues in Access 2003):
a) build the SELECT statement
b) assign it to the SQL property of a query (QueryDef)
c) use the query with DoCmd.TransferSpreadsheet to export the data.

2) Assuming you're using Visual Basic: if you want the names of the
fields, use something along the lines of this air code. If you want to
display the data, study the VB help on recordsets, data controls and (I
think) the DataGrid control. If you still can't get started, ask in a
Visual Basic group rather than an Access one.

Dim odbE As DAO.DBEngine
Dim odbD As DAO.Database
Dim F as DAO.Field

Set odbE = New DAO.DBEngine
Set odbD = dbE.OpenDatabase("D:\folder\file.mdb")

For Each F in odbD.QueryDefs("MyQuery").Fields
Debug.Print F.Name
'put them into a listbox perhaps
Next

odbD.Close
Set odbD = Nothing
Set odbE = Nothing






On Sun, 19 Oct 2003 06:59:29 -0700, "faddrick"

Hello,
I have the following doubts ,
1) how do i transfer the data's from Access to Excel based
on users condition using Vb as front end?
2) How do i display the fields to the end user from a ms
access query in vb ?

plzz reply me asap.

Faddrick


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Faddrick,

If you're writing a Visual Basic application to move data from an
Access/Jet (mdb) database to an Excel worksheet, I suggest you ask in a
Visual Basic newsgroup (e.g. microsoft.public.vb.database.dao, which is
for people using Jet with VB). The general techniques will be as I
outlined in my first message, but Visual Basic forms and controls don't
work quite the same as their Access equivalents.


I tried your code in vb but i couldnt get the expected
output.


Let me explain to u what iam looking for .
i have two tables table a and table b

table a has the fields empno,empname,sex,age.

table b has the fields experience,salary,qualification.

i created a query in msaccess named empquery.

naturally it will display the selected fields.

now i want to call empquery in visual basic.

for that i have created a button.
when user clicks this button the query name,empquery,
should appear in
a combobox and also all the fields in the
query,empquery,must be
displayed in a list box ,named available fields.

i have one more list box named selected fields.
user selects the required fields ,to be displayed in
excel,from
available fields to selected fields.

i have a button which takes user to an another form where
the user
can give some condition , for example empno >= 1000
i wanted to transfer all the datas where empno greater
than or equal
to 1000 to excel.

i hope my problem is very clear to u now.

pl help me.








-----Original Message-----
Not without more information on exactly what you're trying to achieve,
whether you're working in VB, Excel or Access, what you've tried and how
it doesn't meet your expectations.

Thanks for the information but still i couldnt get the
expected out put.
so if u have any other ideas can u just pass it to me.

tq
bye
faddrick
-----Original Message-----
Hi Faddrick,

1) If you're using Visual Basic or Excel VBA (as opposed
to Access VBA)
to work with data in an mdb file,
a) build a SQL SELECT statement incorporating the
user's conditions
b) open a recordset on this
c) use Excel's Range.CopyFromRecordset method to put
the data into
your worksheet.

If you're using Access VBA, you could do the same, or
else (with less
flexibility and some security issues in Access 2003):
a) build the SELECT statement
b) assign it to the SQL property of a query (QueryDef)
c) use the query with DoCmd.TransferSpreadsheet to
export the data.

2) Assuming you're using Visual Basic: if you want the
names of the
fields, use something along the lines of this air code.
If you want to
display the data, study the VB help on recordsets, data
controls and (I
think) the DataGrid control. If you still can't get
started, ask in a
Visual Basic group rather than an Access one.

Dim odbE As DAO.DBEngine
Dim odbD As DAO.Database
Dim F as DAO.Field

Set odbE = New DAO.DBEngine
Set odbD = dbE.OpenDatabase("D:\folder\file.mdb")

For Each F in odbD.QueryDefs("MyQuery").Fields
Debug.Print F.Name
'put them into a listbox perhaps
Next

odbD.Close
Set odbD = Nothing
Set odbE = Nothing






On Sun, 19 Oct 2003 06:59:29 -0700, "faddrick"

Hello,
I have the following doubts ,
1) how do i transfer the data's from Access to Excel
based
on users condition using Vb as front end?
2) How do i display the fields to the end user from a ms
access query in vb ?

plzz reply me asap.

Faddrick


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Back
Top