Solved

Create MS-Access Table via Delphi

Posted on 2013-01-17
11
2,393 Views
Last Modified: 2013-02-08
Hello all,

First of all, I didn't write this question except after hammering the entire section and the Internet in general for days looking for the right answer, but unfortunately, some answers are too old some are not complete etc.

I saw the chapters in About.com Delphi section but it requires ADOX 2.x dll which is not in my system. I have msadox.dll (ver 6) and I tried to import it but Delphi XE2 seems not to recognize the file. So I decided to go with the old-fashioned way.

Anyway, the program that will create a table after clicking a button. Everything is set up perfectly except the query sentence that will be sent to the database.

Here is the code:

procedure TAddIndivdualForm.OkClick(Sender: TObject);
var
Name: string;
QString: string;
begin
Name := NameEdit.Text;

QString:= 'CREATE TABLE  ' + Name + ' (ProcessCaleneder DateTime, IndivName text(30), 
Position text(30), Mobile text(20), Salary Currency, Notes text(255))';

with MainForm.IndivdualsQuery do
begin
  Close;
  ConnectionString := 
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Mark\Desktop\Database.mdb;';
  SQL.Clear;
  SQL.Add(QString);
  ExecSQL;
end;

Open in new window


The result after clicking the button is: Syntax error in field definition. (because of the query)

the SQL.Add parameters were those of QString variable. But I took the string out, chopped it up a little and added string variables in between. String concatenation could be a huge mess sometimes in my personal opinion.

I tried to find the MS-Access queries reference on the net but I couldn't. I want to know how the sql queries were formed ?

So, to keep the post organized, here are my questions:

1. If you can correct the query sentence, I appreciate that.
2. Where do I find the queries reference ?
3. How do I make the XE2 IDE recognize ADOX 6.0 ?
4. The jet engine used in my example is ver 4.0 and .mdb, how do I make the IDE recognize access 2010 files ? will this enforce the other users of my app to install the distribution package ?
 
I appreciate your help even if you answer a portion of the questions.

Mark.
0
Comment
Question by:gxs
11 Comments
 
LVL 2

Expert Comment

by:MMTadmin
ID: 38789946
1 - What is the error when you execute the sql string, first point of attack
2 - ?
3 - ADOX from memory (its been a while! was long winded - I would stick with SQL!)
4 - Make the connection string based on a setting for the IDE either from an ini file, xml file or registry setting when you install the application
0
 
LVL 1

Author Comment

by:gxs
ID: 38789984
Thanks MMTadmin for you reply,

1. It is in the SQL sentence, because when I delete some parts, It works fine.
2. I mean the access sql queries reference ( Every sql language has its own sql queries methods )
3. I considered many solutions such as SQL, Absolute Database but the program is so simple and the database is local.
4. The connection string wouldn't be valid if the IDE doesn't recognize the appropriate database driver.
0
 
LVL 1

Author Comment

by:gxs
ID: 38790010
Regarding the recognition of *.accdb files.

I have seen this video a couple of days ago. It is about this specific subject.

http://www.youtube.com/watch?v=E_2hrER9oho

My IDE doesn't show the same driver when the guy in the video navigates to Microsoft Access Driver (*.accdb).

I have installed Microsoft Access Database Engine 2010 Redistributable, but still, no luck :p

and by the way, the language in the video is Portuguese and I don't speak it.
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 1

Author Comment

by:gxs
ID: 38790150
AAAh, finally, I have stabbed the query problem in the heart X__X

The word ([Position] text(30)) appears to be somehow a reserved word ? Because of that I was asking about the queries reference.

Hope you answer the rest of the questions
0
 
LVL 1

Accepted Solution

by:
gxs earned 0 total points
ID: 38790196
Well, Today was a weird one to me,

Above all of the problems above, the final problem was replacing the fields names in the SQL query string  with Arabic ones.

I modified the string accidentally into:

QString:= 'CREATE TABLE ' + Name + ' ([¿¿¿¿¿¿¿] DateTime, [¿¿¿¿¿] text(30), [¿¿¿¿¿¿ ¿¿¿¿¿¿¿] text(30), [¿¿¿¿¿¿] text(20), [¿¿¿¿¿¿] Currency, [¿¿¿¿¿¿¿] text(255))';

It worked like charm! without the square brackets, it would give me a ms-access syntax error due to the string being manipulated by the Arabic words.

I'm just amazed by how such a very tiny modification solved the problem X__X
0
 
LVL 1

Author Comment

by:gxs
ID: 38790199
^^^ experts-exchange doesn't support UNICODE? ooooh you kidding me :"(
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38791929
in google type "ms access sql reference"
or any other "database type sql reference"

i have to admit, it's not user friendly navigating the websites to find the accurate statement on msdn for access
an index somewhere would be way more helpful

this list the syntax on the statements
http://office.microsoft.com/en-us/access-help/create-or-modify-tables-or-indexes-by-using-a-data-definition-query-HA010341600.aspx?CTT=1

reserverd word list
http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx?CTT=1

looks like position is not in this list

the square brackets are usually a must in access
0
 
LVL 1

Author Comment

by:gxs
ID: 38795258
No one is willing to help :"(

------------------------------------------

For days and years to come and for those who are looking for creating tables:

Here is a simpler way using TADOCommander:

procedure TAddStaffForm.OkClick(Sender: TObject);
var
Name: string;
QString: string;

begin
Name := NameEdit.Text;
QString:= 'CREATE TABLE ' + '[' + Name + ']' + ' ([¿¿¿¿¿¿¿] DateTime, [¿¿¿¿¿¿ ¿¿¿¿¿¿¿] text(30), [¿¿¿¿¿¿] text(20), [¿¿¿¿¿¿] Currency, [¿¿¿¿¿¿¿] text(255))';

// StaffCommander is a TADOCommander component
MainForm.StaffCommander.CommandText := QString;
MainForm.StaffCommander.Execute;

// StaffPuller is a TADOTable component
MainForm.StaffPuller.Active := False;
MainForm.StaffPuller.TableName := name;
MainForm.StaffPuller.Active := True;
end;

Open in new window


The square brackets are added to avoid foreign language mistakes in the SQL string. They are not needed if the language is English, French etc. But recommended.
0
 
LVL 19

Expert Comment

by:Thommy
ID: 38797339
I've been using KaDao Components for Delphi and MS Access for years without any problems.

Perhaps this may also be an option for you...
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38807983
You probably need to cast all your field and table names into ASCII.  The database engine you are using is probably old enough that it is not unicode capable.
0
 
LVL 1

Author Closing Comment

by:gxs
ID: 38867424
The best solution
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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