The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
MS Access
Richy
Registered User regular
Registered User regular
I have a weird problem with MS Access.
So I have a DB composed of multiple tables in MS Access, and I'm handling it (adding stuff, querying, and removing stuff) from a C# program. I add columns into the tables using this code:
And I delete them using this code:
Seems straightforward, and it works. When I look into Access after the ADD command, the columns are there, and when I look after the DROP command, the columns are gone.
But then in one execution I ADDed too many columns (over Access' limit of 255) and I got an error "too many fields defined". Damn. I DROPped all the fields, ran it again, and at the first ADD it crashed with the same error. Weird. I went into Access to make sure, and the columns are really gone. I tried adding manually a column in Access, and got the same error.
It seems that Access still "remembers" somehow the columns being there. Why? And more importantly, how do I delete them permanently so that I can use my table and ADD stuff into it again?
So I have a DB composed of multiple tables in MS Access, and I'm handling it (adding stuff, querying, and removing stuff) from a C# program. I add columns into the tables using this code:
using (OleDbCommand DBCmd = new OleDbCommad("ALTER TABLE TableName ADD ColumnName varchar(255) null", accessConnection))
{ DBCmd.ExecuteNonQuery(); }
And I delete them using this code:
using (OleDbCommand DBCmd = new OleDbCommad("ALTER TABLE TableName DROP COLUMN ColumnName", accessConnection))
{ DBCmd.ExecuteNonQuery(); }
Seems straightforward, and it works. When I look into Access after the ADD command, the columns are there, and when I look after the DROP command, the columns are gone.
But then in one execution I ADDed too many columns (over Access' limit of 255) and I got an error "too many fields defined". Damn. I DROPped all the fields, ran it again, and at the first ADD it crashed with the same error. Weird. I went into Access to make sure, and the columns are really gone. I tried adding manually a column in Access, and got the same error.
It seems that Access still "remembers" somehow the columns being there. Why? And more importantly, how do I delete them permanently so that I can use my table and ADD stuff into it again?
Richy on
0
Posts
Now, is there a way to call that command from my C# program?
First Google result. It can be done in VBA in Access, so why not from C#?
I believe it's the "ActiveX Extensions for DDL and Security" (also known as ADOX), read more about it here:
http://www.4guysfromrolla.com/webtech/013101-1.shtml
Anyways, it's an old-school COM component, but you can still use it in C# through Interop.
I don't remember the exact commands, but I'm 99% sure you can re-compress the database programmatically using ADOX. You'll have to do a bit more investigation on it though.
Also, it provides a much more pleasant interface for building tables than SQL if you felt inclined to use it for that purpose.
(I'm an old-school VB programmer at heart, and I remember finding out about ADOX as being a development that really made database programming back in the old school VB days much easier).
In fact, I had found this code last night, and I've been trying to include it in my program. But when I try to include a reference to the JRO dll, I get an error "Assembly generation failed -- Referenced assembly does not have a strong name". If I don't include it, of course, I get compilation errors because it doesn't know what a JRO.JetEngine is.
I've been reading the MS documentation on this problem, and it's not helping. Any ideas?