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

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.

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

well, then more information is needed,

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

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.

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.

Listening...

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

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

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

regards
Smurff

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.

To be more exact, it replies...

  "Syntax error in field definition"

MoonCalf.

listening...

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

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.

? hmm...

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.

well, asked myself at community support at

https://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

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

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

OK. How much do you suggest they earned?

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

https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20152970
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20152968

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

All,


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


Thanks
Smurff