oreg
asked on
Exporting a database
Hi,
I want to export a database file to some other formats.
I have no real idea if this is easy or hard, but it must
be done from within VB (VB 5 Pro), using only what comes with VB (ie: no extra controls, or 'user must have xxx installed on their system).
Let's assume I have a database file called main.mdb (Access)
Then I have 4 tables : table1, table2, table3, table4
How would I export the information in these tables, to
1 - ASCII
2 - Excel
3 - Any spreadsheet (same stuff as Excel?, perhaps a Comma separated field?)
My program simply uses the Data control to add, view, delete, and update my database...and I want an option to export the contents to those formats.
Thanks for any help,
oreg
(ps: I will give bonus points for a great answer)
I want to export a database file to some other formats.
I have no real idea if this is easy or hard, but it must
be done from within VB (VB 5 Pro), using only what comes with VB (ie: no extra controls, or 'user must have xxx installed on their system).
Let's assume I have a database file called main.mdb (Access)
Then I have 4 tables : table1, table2, table3, table4
How would I export the information in these tables, to
1 - ASCII
2 - Excel
3 - Any spreadsheet (same stuff as Excel?, perhaps a Comma separated field?)
My program simply uses the Data control to add, view, delete, and update my database...and I want an option to export the contents to those formats.
Thanks for any help,
oreg
(ps: I will give bonus points for a great answer)
On a command button on click event
Dim db As New Access.Application
Set db = GetObject("c:\my documents\db2.mdb")'change
'make sure you have access and excel in your vb references
with db
DoCmd.TransferText acExportDelim, "", "table or quey name to export to textfile", "the file you want it saved to.txt"
'if you want to send to an excel file:
DoCmd.TransferSpreadsheet acExport, 8, "tablenameorqueryname", "c:\my documents\filename.xls", False, ""
'8 means the version of excel 8 means version 97
end with
If you need further help, let me know!
Specialist
Dim db As New Access.Application
Set db = GetObject("c:\my documents\db2.mdb")'change
'make sure you have access and excel in your vb references
with db
DoCmd.TransferText acExportDelim, "", "table or quey name to export to textfile", "the file you want it saved to.txt"
'if you want to send to an excel file:
DoCmd.TransferSpreadsheet acExport, 8, "tablenameorqueryname", "c:\my documents\filename.xls", False, ""
'8 means the version of excel 8 means version 97
end with
If you need further help, let me know!
Specialist
The Specialist is correct if you have Access and Excel loaded on the client machines...
You can also accomplish this through DAO code to get the tables into an ASCII text file.
You can also accomplish this through DAO code to get the tables into an ASCII text file.
ASKER
Wow, these responses were very fast, thank you for that. I am not sure how the exported data should look, ie: separate files or not...but that would be fine for me...each table = each file.
SPECIALIST, thanks for your answer, but like mcix said, this means that Access and Excel _must_ be installed on the client machine, and right now that is not the way I want to go...I can hear the screams of Wordperfect users now as I tell them they must install office to run my puny program :)
Thanks,
oreg
SPECIALIST, thanks for your answer, but like mcix said, this means that Access and Excel _must_ be installed on the client machine, and right now that is not the way I want to go...I can hear the screams of Wordperfect users now as I tell them they must install office to run my puny program :)
Thanks,
oreg
I have a vb class that can split it into CSV files...
Do you want it?
Do you want it?
I misread your question did not realize they did not have them installed.
Specialist
Specialist
ASKER
No prob Specialist. Sure mcix, answer the question and you can post it, or send it to me via email : murphyb@uoguelph.ca
If that works out, I will give you the points. CSV is probably all I want anyhow.
Thanks,
oreg
If that works out, I will give you the points. CSV is probably all I want anyhow.
Thanks,
oreg
You got it...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I forgot that FILE_TYPE_DELIMITED is a defined constant in my class, it should be declared as:
Private Const FILE_TYPE_DELIMITED As String = ".CSV"
Private Const FILE_TYPE_DELIMITED As String = ".CSV"
oreg,
There are some errors in the code I posted...
I was cutting out my class lib and added some code to make it work from just a form...
You should get the good code in e-mail...
There are some errors in the code I posted...
I was cutting out my class lib and added some code to make it work from just a form...
You should get the good code in e-mail...
Did you get the email?
ASKER
Hi,
Yeah I got the email, I will try the code tommorrow when I have time...I skimmed it and it looks like it should work, although I will admit to not having much experience in this matter.
Thanks, I will let you know soon how it goes,
oreg
Yeah I got the email, I will try the code tommorrow when I have time...I skimmed it and it looks like it should work, although I will admit to not having much experience in this matter.
Thanks, I will let you know soon how it goes,
oreg
Fair enough...
Still waiting....
ASKER
Hi there,
I am really sorry this took so long, your code worked really well. As promised, I am adding some bonus points.
Take care, and thanks,
oreg
I am really sorry this took so long, your code worked really well. As promised, I am adding some bonus points.
Take care, and thanks,
oreg
Glad it worked out for you....
You have multiple tables, when you export do you want each table to be a separate
1. file in ASCII.
2. worksheet, workbook in Excel.
3. for the unknown export?