Solved

Create an Access Database, then create and populate a table.

Posted on 2001-07-18
25
148 Views
Last Modified: 2010-04-06
Hi,

Sorry about the not too high points, but it's all I've got at the minute cos I've given the rest to you lot! :)

I need to be able to (as the title suggests)

     1)  Create a new access database (Access 2000).
     2)  Create a new table within this database.
     3)  Populate the table with my data.

Basically, my boss wants to run reports on a log file that I currently produce and it would be nice if I could just get an Access DB to run the reports from.

Thanks in advance for any help,

MoonCalf.
(Brian)
0
Comment
Question by:MoonCalf
  • 8
  • 6
  • 4
  • +4
25 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 20 total points
Comment Utility
a codefragment from psutton for creating an access-database

unit Mdb;

interface
uses
 Windows,ComObj, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
 StdCtrls;

procedure BldMdb(FileName:String);
implementation
const
 dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0';
 dbVersion30 = 32; {choose this for Access 95 database}
 dbEncrypt = 2;
var
 DBEngine: Variant;
 Workspace: Variant;

procedure BldMdb(FileName:String);

begin
 DBEngine := CreateOleObject('DAO.DBEngine.35');
 Workspace := DBEngine.Workspaces[0];
 Try
 Workspace.CreateDatabase(Filename, dbLangGeneral, dbEncrypt);
 Except
 end;
end;

end.

after creating the database you can use an sql-statement for creating a table. see access refernce for the syntax.

after creating the table you insert your data

meikl ;-)
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
Thanks meikl, but you have only really answered part of the question (not tried it yet, either!)

I'll give it a go, but I will need help on the rest of the question so this is by no means answered yet.

MoonCalf.
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
Hi,

I've got the following code from EE and I haven't tested it. After creating the db, use TQuery.ExecSQL with 'CREATE TABLE' SQL statements to create tables.

const
 ODBC_ADD_DSN = 1; // Add data source
 ODBC_CONFIG_DSN = 2; // Configure (edit) data source
 ODBC_REMOVE_DSN = 3; // Remove data source
 ODBC_ADD_SYS_DSN = 4; // add a system DSN
 ODBC_CONFIG_SYS_DSN = 5; // Configure a system DSN
 ODBC_REMOVE_SYS_DSN = 6; // remove a system DSN
 ODBC_REMOVE_DEFAULT_DSN = 7; // remove the default DSN

function SQLConfigDataSource(
 hwndParent: HWND;
 fRequest: WORD;
 lpszDriver: LPCSTR;
 lpszAttributes: LPCSTR): BOOL; stdcall; external 'ODBCCP32.DLL';

// 'CREATE_DBV3=' for MS Access97
// 'CREATE_DBV4=' for MS Access2000
procedure TForm1.CreateClick(Sender: TObject);
var
 DbName: String;
begin
 DbName := 'C:\Test.mdb';
 if not SQLConfigDataSource(0, ODBC_ADD_DSN,
   'Microsoft Access Driver (*.mdb)', PChar(
   'CREATE_DBV4=' + DbName + #0)) then
   MessageDlg('Unable to create ' + DbName, mtError, [mbOk], 0);
end;

procedure TForm1.RepairClick(Sender: TObject);
var
 DbName: String;
begin
 DbName := 'C:\Test.mdb';
 if not SQLConfigDataSource(0, ODBC_ADD_DSN,
   'Microsoft Access Driver (*.mdb)', PChar(
   'REPAIR_DB=' + DbName + #0)) then
   MessageDlg('Unable to repair ' + DbName, mtError, [mbOk], 0); end;

procedure TForm1.CompressClick(Sender: TObject);
var
 DbName, DbName2: String;
begin
 DbName := 'C:\Test.mdb';
 DbName2 := 'C:\Test2.mdb';
 if not SQLConfigDataSource(0, ODBC_ADD_DSN,
   'Microsoft Access Driver (*.mdb)', PChar(
   'COMPACT_DB=' + DbName + ' ' + DbName2 + ' General'#0)) then
    MessageDlg('Unable to compress ' + DbName, mtError, [mbOk], 0);
end;

Regards, Geo
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
well, then more information is needed,

this log-file is a flatfile?
what about its fields and delimitter or is it fixedsize?
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
The logfile is not your concern.  I'll do the import, I just need to get the table created.  (Each table will have a name something like LOG010718 - LOG + the date in a sortable order).

If I can simply use an insert statement with a TQuery then I'm fine with that.  I'll try a create table and see how I get on.

MoonCalf.
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
I think that this bit is wrong....

        DBEngine := CreateOleObject('DAO.DBEngine.35');

The database is an older version of access.  I'm using Access 2000.

MoonCalf.
0
 
LVL 6

Expert Comment

by:Jaymol
Comment Utility
Listening...
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
Okay, I've created the Database and connected to it succesfully with the ADO components.

Now I'm having trouble with the SQL statement to create the table.  (Syntax error, apparently!)

Could someone give me an example of the SQL for CREATE TABLE and INSERT INTO for an access database?

Thanks,

MoonCalf
0
 
LVL 3

Expert Comment

by:smurff
Comment Utility
create a table:

CREATE TABLE employee
  (number INTEGER,
   name CHAR(20),
   rating FLOAT);

then to insert data:

Now you can enter data into each of these tables. Using the syntax

INSERT INTO employee VALUES
  (Number, "Name", rating);

add the Employee table data from Table B. For example, to add the first employee's information, you'll enter

INSERT INTO employee VALUES
  (1001, "Dave Otterson", .98);


Regards
Smurff
0
 
LVL 3

Expert Comment

by:smurff
Comment Utility
Brian,
A good basic DB site for these kinda things is:
http://www.elementkjournals.com/ddj/back/ddj1_1h.htm

regards
Smurff
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
Thanks Smurff, but I've tried something similar....

CREATE TABLE LOG010718 (Field1 CHAR(50), Field2 CHAR(20), Field3 CHAR(20), Field4 CHAR(20), Field5 CHAR(20), Field6 CHAR(10));

...replies that there is a syntax error and it won't execute.
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
To be more exact, it replies...

  "Syntax error in field definition"

MoonCalf.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
listening...
0
 
LVL 3

Expert Comment

by:smurff
Comment Utility
From looking into MSDN it could depend on how you are connecting to whether you use CHAR(20) or STRING(20)
How are you connecting? or executing the SQL is it like:

myDB.ExecuteSQL("CREATE TABLE LOG123456 (FIELD1 TEXT(10),FIELD2 TEXT(10))")

Your source example?

regards
Smurff
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
Thanks for the help so far guys, but I needed a quick answer to this, 100% complete.  I've had to just use a TQuery and do a CREATE TABLE creating a paradox table.  I'll simply import that into Access to create the reports.

Thanks again,

MoonCalf.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
? hmm...
0
 
LVL 1

Author Comment

by:MoonCalf
Comment Utility
I am deleting this question.

If this question is opened again, I'll simply ask EE community service to delete it, so I suggest that comments like "?hmmm" should be left pre-keyboard.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
well, asked myself at community support at

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20152762

because i didn't agree with you for simple deleting the q,
even you asked three q's into one and all three q's are answered, well not by one expert

regards
meikl
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
Hello all,

I can assist in splitting the points for this question between a number of experts.

Brian, let me know if that would be a potential solution to the situation.

modder
Community Support Moderator@Experts-Exchange
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
to brian,

that was not what i guessed

i would have preferred a splitting between geobul and smurff, because i didn't know if my suggestion really works with access2000, because its an older one from my knowledgebase, but i would guess geobuls suggestion is a tested one using the odbc-api and smurff gives you some suggestions how to create a table with sql and inserts records with sql.

anyway thanks for the points,
even i was not after this.

to modder,
what do you think about a grade to geobul and smurff for investing time and giving (from my point of view) good suggestions?

meikl
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
OK. How much do you suggest they earned?
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
hi modder,

i would suggest to both the half of the original score,
and no one for me (too late i guess)

but thats only my own opinion.
(any other meanings are welcome)

meikl
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
0
 
LVL 17

Expert Comment

by:geobul
Comment Utility
meikl,
Thank you very much for your way of playing the game and your efforts. There aren't so many people here who would do such a thing. I was pleasantly surprised.

Best regards, Geo
0
 
LVL 3

Expert Comment

by:smurff
Comment Utility
All,


kretzschmar : Thanks for your comments.
Modder: Again thanks.
Geobul: nice code!
MoonCalf: Good luck with your code.


Thanks
Smurff
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now