Access Allow Zero Length from Delphi 1

[Opieo]

Okay, so as the title suggests, I am making a table in access from Delphi.
My problem is that I need to set the AllowZeroLength property to true from Delphi, since it is by default set to 'No'.
Is there any way to do this from Delphi rather than having to open up the database or something?
I tried searching the forum for an answer but was unable to find anything. I also tried searching several other places but to no real avail other than a couple people saying it just isn't possible, which I just cannot accept.
I generally prefer to be self sufficient and only post when I really need help. I feel like I am admitting weakness even though I know that isn't the case. But that is my own moral issue to deal with. Just that reviewing parts of this board in my search, there are quite a few of you who have posted about a lot of things that I still have not seen or dealt with in Delphi and it just makes me feel small. I know I cannot expect myself to know everything, especially since I have really only used Delphi for about one and a half solid years doing mostly no really large apps so far (I just turned 23 too). But again, that is a different issue and I digress. How about that Zero Length issue?

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Griffyn]

I'd deal with those psychological issues first and then come back to this one (joke)

taken from another forum

[navy][i]// for automatic syntax highlighting see faq102-6487 
[/i][/navy][b]uses[/b]
  ADOX_TLB;

[b]procedure[/b] TForm1.Button1Click(Sender: TObject);
[b]var[/b]
  DS: [b]string[/b];
  Catalog: _Catalog;
  DBName : [b]string[/b];
[b]begin[/b]
  DBName := [teal]'c:\test.mdb'[/teal];
  Catalog := CoCatalog.Create;
  DS := [teal]'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='[/teal] + DBName + [teal]';Jet OLEDB:Engine Type=5'[/teal];
  Catalog.Set_ActiveConnection(DS);
  Catalog.Tables[[teal]'MyTable'[/teal]].Columns[[teal]'MyField'[/teal]].Properties[[teal]'Jet OLEDB:Allow Zero Length'[/teal]].Value := True;
[b]end[/b];

You will need to import the ADOX_TLB unit. Do this by going to the Project menu, then Import Type Library. Choose Microsoft ADo Ext. 2.x for DLL and security

I've tested this code, and it worked for me.

 

[Opieo]

Hey Griffyn, thanks for the pointers. I am still getting one tiny error though.
Everything works fine until I go to change the property with the last line.
Then I get an error 'Item cannot be found in the collection corresponding to the requested name or ordinal.'
It is not a crippling error, and the program continues, but it skips the rest of my function.

Additional Info:
I have verified that the column names are accurate (I copied them a 2nd time just to make sure).
I copy over the connection string that creates the table just fine so I know that it is correct.

      Catalog := CoCatalog.Create;
      Catalog.Set_ActiveConnection(Form1.ADOConnection1.ConnectionString);
      cs := 'SELECT * INTO ' + EWOS + ' FROM TABLTEMP WHERE 0 = 1;';
      ADOC.CommandText := cs;
      ADOC.Execute; // Create EWO Table
      Catalog.Tables[EWOS].Columns['version'].Properties['Jet OLEDB:Allow Zero Length'].Value := True;

Any further pointers?


~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[lespaul]

looks like you're missing the quotes around the Tables name collection:

      Catalog := CoCatalog.Create;
      Catalog.Set_ActiveConnection(Form1.ADOConnection1.ConnectionString);
      cs := 'SELECT * INTO ' + EWOS + ' FROM TABLTEMP WHERE 0 = 1;';
      ADOC.CommandText := cs;
      ADOC.Execute; // Create EWO Table
      Catalog.Tables[[COLOR=red]'[/color]EWOS[COLOR=red]'[/color]].Columns['version'].Properties['Jet OLEDB:Allow Zero Length'].Value := True;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lespaul]

my bad, looks like EWOS is a variable declared somewhere prior in the code, maybe:

Catalog := CoCatalog.Create;
      Catalog.Set_ActiveConnection(Form1.ADOConnection1.ConnectionString);
      cs := 'SELECT * INTO ' + EWOS + ' FROM TABLTEMP WHERE 0 = 1;';
      ADOC.CommandText := cs;
      ADOC.Execute; // Create EWO Table
      Catalog.Tables[[b]QuotedStr(EWOS)[/b]].Columns['version'].Properties['Jet OLEDB:Allow Zero Length'].Value := True;

 

[Griffyn]

does the value of EWOS have brackets around it? eg

  EWOS := '[My Table]';

If so, try removing the brackets.

 

[Opieo]

I had a problem that QuotedStr fixed before. Was hoping it would fix this too, but sadly it did not.
The names are made right before the code I pasted:

EWOS := EditInit.Text + 'EWO';

I also found elsewhere people mentioning getting that error string when the table had 0 records, granted that the people getting the error with that reason all seemed to be programming in ASP.

I can see after trying the code that the table gets created perfectly (as it did before I needed to do this), but it just errors out at the line where I go to set the Allow Zero Length property.
I even re-checked to make sure that I had the ADOX_TLB there just fine and it is for sure there (would have guessed that since it wasn't giving me unrecognized item errors).

Am still at a loss, although I feel closer to the solution.
Thank you for your continued efforts to assist me btw. =)

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Opieo]

Wanted to note, I leave at noon (Eastern US) today for a business trip to France and will be out the rest of the week, so it may be awhile before I update on anything here.

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Opieo]

Ack, no replies!
Any final thoughts?

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Griffyn]

Set up a test program using my example code and make sure it works for you. Then it should be a simple matter of comparing what works in the example and what's different in your actual code. See how that goes.

 

[Opieo]

Hmm, trying just your code in a test form with test everything, it works perfectly fine.
I would have suspected as much.
I will just play around with the differences between the bare bones and my program until it works.
Thank you much Griffyn and lespaul!

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Opieo]

Okay, have tried direly to no avail.
Here is the code of the super simple program:

procedure TForm1.Button2Click(Sender: TObject);
 var
  cs, DS : String;
  Catalog : _Catalog;
  DBName : String;

 begin
  DBName := [COLOR=blue]'D:\testdb.mdb'[/color];
  Catalog := CoCatalog.Create;
  DS := [COLOR=blue]'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='[/color] + DBName + [COLOR=blue]';Jet OLEDB:Engine Type=5'[/color];
  ADOConnection1.ConnectionString := DS;
  cs := [COLOR=blue]'SELECT * INTO newTabl FROM testTabl WHERE 0 = 1;'[/color];
  ADOC.CommandText := cs;
  ADOC.Execute; // Create EWO Table
  Catalog.Set_ActiveConnection(DS);
  Catalog.Tables[[COLOR=blue]'newTabl'[/color]].Columns[[COLOR=blue]'testfield'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;
 end;

It works perfectly. It has ADOX_TLB in the uses. So does my program which is not working:

procedure TForm9.ButModClick(Sender: TObject);
 var
  cs : String[250];
  Catalog : _Catalog;
  EWOS, Finish  : String;

 begin
    Form8.TablProg.Active := True;
    if not (Form8.TablProg.Locate ([COLOR=blue]'program'[/color], EditInit.Text, [])) then
     begin
      EWOS := EditInit.Text + [COLOR=blue]'EWO'[/color];
      Finish := EditInit.Text + [COLOR=blue]'Finish'[/color];    // Short & simple, same result
      Catalog := CoCatalog.Create;
      cs := [COLOR=blue]'SELECT * INTO '[/color] + EWOS + [COLOR=blue]' FROM GMX024EWO WHERE 0 = 1;'[/color];
      ADOC.CommandText := cs;
      ADOC.Execute; // Create EWO Table
      Catalog.Set_ActiveConnection(Form1.ADOConnection1.ConnectionString);
      Catalog.Tables[QuotedStr(EWOS)].Columns[[COLOR=blue]'internal'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;
      Catalog.Tables[QuotedStr(EWOS)].Columns[[COLOR=blue]'version'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;

My program still gives me the Item cannot be found in the collection corresponding to the requested name or ordinal. error. I have even tried adding a line to unactivate TablProg on Form8, just in case, but it did not help.
I cut down the connection string in my large program to be an exact duplicate to that of the small program as well but it did not help. (The only thing I removed to get that was the DB Password)

Any last possible thoughts?
I am sorry to keep bugging about this. I really am trying =P

~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.

 

[Griffyn]

I'd get rid of the QuotedStr wrapper to start with, as I see no reason for it to be there. Also, please provide the actual running value for EWOS (by stepping through your code and using the Debug Watch window). And check that this table does exist, and is not a Query in your database.

 

[Opieo]

Okay, problem fixed.
Removing the QuotedStr fixed it. I figured since quoting the string in the other fields was how it worked that this needed it as well. I did need to do that in a query elsewhere. I used ShowMessage-s here and there to verify how things were showing up and they all looked right. Just the difference in a variable vs. explicit text.

There are no queries in the database itself. Any queries I make will all be dynamic when I get to the reporting stage of my program (not required with V1.0 oddly enough).

I also rearranged it since I got a few other errors.
In my whole process I make 2 tables. Now I make both tables using that nice cheap copying method ahead of time, then go through them both and set the zero length properties.

I do run into an error later on if I try to release (Catalog._Release) the Catalog, but I just hope that Delphi takes care of that when the form closes out so I don't have a memory leak. Currently the only way I have around it.

Here is the relevant working code for reference for anyone who reads this later:

 var
  cs : String[250];
  Catalog : _Catalog;
  EWOS, Finish  : String;

     begin
      EWOS := EditInit.Text + [COLOR=blue]'EWO'[/color];
      Finish := EditInit.Text + [COLOR=blue]'Finish'[/color];  
      cs := [COLOR=blue]'SELECT * INTO '[/color] + EWOS + [COLOR=blue]' FROM GMX024EWO WHERE 0 = 1;'[/color];
      ADOC.CommandText := cs;
      ADOC.Execute; // Create EWO Table
      cs := [COLOR=blue]'SELECT * INTO '[/color] + Finish + [COLOR=blue]' FROM GMX024Finish WHERE 0 = 1;'[/color];
      ADOC.CommandText := cs;   // For Finished parts
      ADOC.Execute;
      Catalog := CoCatalog.Create;   // For allowing Zero Length in text fields
      Catalog.Set_ActiveConnection(Form1.ADOConnection1.ConnectionString);
      Catalog.Tables[EWOS].Columns[[COLOR=blue]'version'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;
      Catalog.Tables[EWOS].Columns[[COLOR=blue]'EWO'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;
     Catalog.Tables[Finish].Columns[[COLOR=blue]'color'[/color]].Properties[[COLOR=blue]'Jet OLEDB:Allow Zero Length'[/color]].Value := True;

Notes: The GMX024EWO and Finish table already exist. Copying * from them into a new table automatically sets up the whole table with the exact same field names. It preserves the maximum lengths of texts. For numbers it preserves if they are Doubles or Long Integers, etc. Very nice short piece of code that I like. =) I also have more fields then are shown there, but kept it short.

And thanks again for your help guys!


~
Give a man some fire, he will be warm for a day, Set a man on fire, he will be warm for the rest of his life.