blackadder has asked for the wisdom of the Perl Monks concerning the following question:

Hi All

Can I get Perl to create simple MS access table and a query?

Some background information; I have a script that gathers data from a number of sources, then the same script populates an MS Access database. However, before I can use the script I need to create couple of simple tables, ready to receive the data, and a query which basically tie those two tables together like this:

Table1 (saved it as Folders table)

Field Name ........Type 
Folder_ID     Auto-Number (Long Integer, also it is the Key value so n
+o duplicates are allowed)
Folder        Text (Field size 255, Required =>Yes, Allow Zero length 
+=> No)

[download]
Table2 (saved it as Perms table)

Field Name ........Type 
Perms_ID    Auto-Number (Long Integer, also it is the Key value so no 
+duplicates are allowed)
Account    Text (Field size 255, Required =>Yes, Allow Zero length => 
+No)
Type        Text (Field size 50, Required =>Yes, Allow Zero length => 
+Yes)
Folder_ID     Number (Long Integer, the Key value from table1)

[download]
Query (saved it as Main_query) 
Folder    from table1 (Folders_table)
Account      from table2 (Perms_table)    
Type      from table2 (Perms_table)

[download]
The relationship between ‘table1’ and ‘table2’ is one-to many, i.e. Any one folder can have many accounts accessing it.

This is the only bit that I have to set up manually. Can someone please advise me on how can I automate this manual process?

Also, If I wanted to flush the data (i.e. deleting all data but keeping the table and query designs), is there away to automate this process? so that the script will check first to see if there was any data in the database (the dsn name for the database is Perms_db)if data exits then delete it all and then populate the databse or just populate the database.

I have searched PM for ‘Creating MS Access tables’, unfortunately I couldn't obtain an example that fits my situation.

Many Thanks in advance.

Replies are listed 'Best First'.
Re: Automating the creation process of MS Access Tables
by alftheo (Scribe) on Sep 24, 2002 at 19:46 UTC
    You want to look at DBI and DBD::ODBC. This will give you a way of sending SQL commands to an ODBC data source.

    Then you want to find out what SQL you want to send...

    For the tables, you can send plain CREATE TABLE statements, but I doubt that you could save the query into the Access database from ODBC.

    If you create the tables and queries beforehand, creating the query is no problem... The only thing then is to find the value of the autonumber field from Table1, and insert that into Table2. I've no idea how to do that, so I'd suggest you add a Folder field (text) to Table2. Then do 

    DELETE * FROM Table1;
DELETE * FROM Table2;

    [download]
    Followed by a number of 
    INSERT INTO Table1 (Folder) VALUES ('myfolder1');
INSERT INTO Table2 (Account, Type, Folder) VALUES ('Account1', 'type1'
+, 'myfolder1');

    [download]
    When you are done, do 
    UPDATE Table2 SET Table2.Folder_ID = Table1.Folder_ID WHERE Table1.Fol
+der = Table2.Folder;

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom