Solved

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

Posted on 2001-07-18
25
153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +4
25 Comments
 
LVL 27

Accepted Solution

by:
kretzschmar earned 20 total points
ID: 6292816
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
ID: 6292829
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
ID: 6292842
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 6292844
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
ID: 6292852
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
ID: 6292906
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
ID: 6293096
Listening...
0
 
LVL 1

Author Comment

by:MoonCalf
ID: 6293147
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
ID: 6293154
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
ID: 6293164
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
ID: 6293173
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
ID: 6293179
To be more exact, it replies...

  "Syntax error in field definition"

MoonCalf.
0
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 6293215
listening...
0
 
LVL 3

Expert Comment

by:smurff
ID: 6293231
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
ID: 6293242
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
ID: 6293504
? hmm...
0
 
LVL 1

Author Comment

by:MoonCalf
ID: 6293524
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
ID: 6293560
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
ID: 6293684
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
ID: 6294340
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
ID: 6294374
OK. How much do you suggest they earned?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6294412
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 17

Expert Comment

by:geobul
ID: 6297011
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
ID: 6297476
All,


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


Thanks
Smurff
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month3 days, 13 hours left to enroll

630 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