exporting each field of my access database to an excel by with different format

[driftking]

hello,



need help about an issue.

i actually have an ms access database with the following format(assuming)

title1	title2	title3	title4	title5	title6
1a	1b	1c	1d	1e	1f
2b	2b	2c	2d	2e	2f
3a	3b	3c	3d	3e	3f
4a	4b	4c	4d	4e	4f

for each row in the ms access database above i need to retrieve an excel in the format below

title1	1a
title2	1b
title3	1c
title4	1d
title5	1e
title6	1f

title1	2a
title2	2b
title3	2c
title4	2d
title5	2e
title6	2f

and so on....

Any suggestion from where i can proceed,what tool can i use??

thanks in advance

[rpeare]

Your details are very sketchy, and assuming this is exactly how your table is set up (there aren't any primary key fields etc) I would tend to create a module and export everything to a .csv.

Assuming your table name is tblMatrix I'd do something like this

Dim fs
Dim fsExportFile
Dim sExportString As String
Dim db As Database
Dim rst As Recordset
Dim fld As Field

Set fs = CreateObject("scripting.filesystemobject")
Set fsExportFile = fs.createtextfile("c:\test\testexport.csv", True)

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblMatrix")

rst.MoveFirst
Do While rst.EOF <> True
    For Each fld In rst.Fields
        sExportString = fld.Name & "," & fld.Value
        fsExportFile.writeline sExportString
    Next

    rst.MoveNext
Loop

Set db = Nothing

[driftking]

hello thanks for the reply. i have put diagrams for viewer to understand the issue easily.

I am not used to ms access db,but have some experience with vb.net codes. i am eager to learn coding in access,can you tell me where to place the code in my database and send me good links from where i can start code in ms access database(i already know the basic,that is how to create table and add data manually).

currently i have created an ms access database and added some data in it but do not know where to put your codes.

Thanks in advance.

ps: i use ms access 2010

[rpeare]

you can put it any number of places you can put it in the ON CLICK event of a button, you can make a module to run it manually, you can attach it any place you are currently running code, I do not know your database, the scope of your matrix table or anything else about your database so where you put it is up to you, the easiest thing to start with is to make a module (an option under the macros ribbon set) dump in the code in a sub main() function change the code to match your database and see if it gives you the results you want, where you go from there is up to you.

[driftking]

Alright,
i reform my question(now that myself have understand the issue clearly ).

i have an access database with a table containing data.
assuming i have 100s of rows in it.
What i want to do is for each row in the table i want to generate an excel sheet with specific fields in the database match specific cell in the excel template(please find attached).

That is if i have 1 hundred rows in my access database....i have to generate 100 excel sheet and rename it automatically with 1 of the field in my database(find an example attached)

i was thinking of making a form in ms access with button in it. on_click (event) it must loop through the database rows and generate excels according to the number of rows.

can you suggest me a solution or how can i realise my idea

ps: i have never work with access form before

attachment
1 ms access(contactDB) : from which i have to extract data
1 excel(<NOM DU CONTACT>the field name from the ms access db): the template

[rpeare]

contacts_DB-Modified.zip

Here's a copy of your database back, I've added a module. In order for the module to work the database and the blank customer contact file (Customer_Blank.xls) must be in the same folder.

Basically what this does is reads through your contacts table, for each item it finds it opens your 'template' file, fills in the appropriate information, then saves the template file with a new name in the same folder the database resides. I've only put in a couple of the fields to show you how to work it, from there you can do whatever you want/add whatever you want in terms of fields.

[driftking]

thank you,
i am downloading it.

if posible can you please prepare a tutorial about these so that i can learn how to make ms access form and commands.(i don't know anything about ms access module but have some experience about vb.net codes)

i will be using ms access in my work place very often and has been doing major use on it before.

kind regards

if i encounter any issue with your program,will keep you inform.

[rpeare]

My tutorials are just looking things up with google, I've just had enough experience to gather bits and pieces from several different sites and put it together. Before I wrote your example I'd never done anything of the sort before so preparing a tutorial would be like the blind leading the blind

If you've had experience with VB that's all scripting in access is, vb code. So you shouldn't have any trouble translating what you know.