Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

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

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
MoonCalf
Asked:
MoonCalf
  • 8
  • 6
  • 4
  • +4
1 Solution
 
kretzschmarCommented:
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
 
MoonCalfAuthor Commented:
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
 
geobulCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
kretzschmarCommented:
well, then more information is needed,

this log-file is a flatfile?
what about its fields and delimitter or is it fixedsize?
0
 
MoonCalfAuthor Commented:
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
 
MoonCalfAuthor Commented:
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
 
JaymolCommented:
Listening...
0
 
MoonCalfAuthor Commented:
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
 
smurffCommented:
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
 
smurffCommented:
Brian,
A good basic DB site for these kinda things is:
http://www.elementkjournals.com/ddj/back/ddj1_1h.htm

regards
Smurff
0
 
MoonCalfAuthor Commented:
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
 
MoonCalfAuthor Commented:
To be more exact, it replies...

  "Syntax error in field definition"

MoonCalf.
0
 
DragonSlayerCommented:
listening...
0
 
smurffCommented:
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
 
MoonCalfAuthor Commented:
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
 
kretzschmarCommented:
? hmm...
0
 
MoonCalfAuthor Commented:
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
 
kretzschmarCommented:
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
 
modderCommented:
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
 
kretzschmarCommented:
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
 
modderCommented:
OK. How much do you suggest they earned?
0
 
kretzschmarCommented:
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
 
geobulCommented:
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
 
smurffCommented:
All,


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


Thanks
Smurff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now