Question Update command is not passing change back to database ... sometimes.

[Runescope]

So, this is quite frustrating and I'm not understanding what the problem is.

To start it off, this is the bit of code:

FFoundRow = FDT.Select("[Code1] = '" & Code1 & "'")
FFoundRow(0).Delete()
FDA.Update(FDS, "Info")

FHCIndex = FHDT.Columns.IndexOf(Code1)
FHDS.Tables("History").Columns.RemoveAt(FHCIndex)
FHDA.Update(FHDS, "History")

Now, what happens is that it removes the row from one dataset and then passes that change to the database (all well and good so far), but then while it removes the column(field) from the second dataset(verified), it doesn't pass that change to the database, and I can't figure out why.

Anyone have any ideas? If you need more info about my code, don't hesitate to ask!

Thank you for your time and attention to this post.

 

[Runescope]

Okay, so after doing some more digging on this wunnerful site, I think I know what the problem is. Apparently the RemoveAt command doesn't actually delete the column, so there are no actual changes to be sent to the database.

Soooooooooooooooooooo my next question then is: How do I delete a column from the database?

 

[Herman]

Why do you want to delete it in the first place? Couldn't you just query for only the fields you need?

Otherwise check the .CanRemove method. If a column is part of a relationship, or other columns depend on this column's value, you cannot remove it.

 

[jmcilhinney]

I'm afraid that your code makes no real sense. What is it that you are actually trying to achieve? Are you trying to remove rows from a database table? Are you trying to remove data from rows in a database table? Are you trying to remove columns from a database table? If it's the third option then why would you be doing such a thing?

 

[Runescope]

Yes, I can see why you would be confused by the code. I am both deleting rows out of one table, and removing columns out of another. I know that removing columns is unusual, but for my program it is kind of necessary. Please don't bother trying to understand the "why" and just accept that I need it to happen.

 

[Herman]

Then I already gave you your answer: you cannot remove a column that is part of a relationship or depended upon by another column. That must be the case since otherwise RemoveAt would have worked.

 

[Runescope]

Hmmm, there is no relationship or dependency that I know of, or can find. So, can you think of why RemoveAt isn't removing the column? Frankly, I'm stumped.

I'll go check the .CanRemove method like you suggested and see what that says.

Edit:
.CanRemove reports that it can indeed be removed. Hmmm.

jmcilhinney said this in another post (http://www.vbdotnetforums.com/datab...-dataset-into-access-database.html#post108928)

If you call Remove or RemoveAt the row gets removed from the collection, as though it was never there. If you do that there's no way to tell the database to delete the original record.
​

This is in reference to removing a row, but it probably applies to the column as well. I can just use an Alter Table SQL command to remove the column though right?

 

[Herman]

Yes. But why ohh why would you do that? It's a complete miss of the point of using a database.

 

[Runescope]

Well, the short version of the why is that it's an access database and it can only have a maximum of 255 columns, and I'm tracking the daily price history of items over several years (over a decade stored so far!). But some users want to track some items, and others want to track other items and the total number of items to be tracked is currently over 50,000. So to allow users to choose their own items to track, I have to be able to allow them to both add and remove columns. Each column being an item and each row being a daily price.

 

[Herman]

I think you just have your table logic backwards... Access is limited to 255 columns, but can do up to 65535 rows. Just use a column for price and a row per item.

 

[Runescope]

I had tried that at the beginning, but it soon became apparent that it wouldn't work. I can't even fit one years worth of information into a table that way, and I have ten years of data so far.

 

[Herman]

It sounds to me like it's time to migrate away from Access and into SQL.

 

[Herman]

You could also organize your data into multiple tables. One table for item, many tables for prices. But that involves a lot of changed code for the front end and a lot more ways things can go wrong.

Switching to SQL from access is almost painless if your code is organized in any decent way (which it should to handle this amount of data anyways) and SQL is much more robust and faster. The ERP product we develop where I work has many clients with financial databases over 2GB, and while we do still support Access databases for our lower end product we strongly recommend clients with Access databases tipping over 100MB to migrate to SQL. The JET file format that Access uses has a reputation at being very easy to corrupt in a multi-user environment, and very slow from all the compacting. It's fine for small businesses that use the minimum the software has to offer, but in my experience such a large DB should just be on a stronger database platform. And with SQL Express being absolutely and completely free there really is no reason not to install it at the client's.

In an ERP mindset, deleting ANY data except from corrupt or temporary data from a database is a crime in itself. Messing around with deleting columns is folly.

BTW we do have and have always had many instances of db transactions not going through completely under Access. Under high load sometimes a single query meant to update 10 fields in 7 linked tables will write the changes properly to only 8 of them, resulting in a large amount of unnecessary bloat in the code to handle and fix those exceptions, and often loss of data for the client. Because they never see a problem until the application starts puking errors and exceptions, and by that time the only way to fix the database is to lose some data. A T-SQL transaction is guaranteed to be applied to the database, not withstanding power loss or abnormal network link issues. And in those cases you can have transactional backups restored in minutes (or seconds!).

Really, do yourself a favor and back away slowly from Access. Code will be lighter and easier to maintain, application will be faster, and overall stability much higher.

 

[jmcilhinney]

I haven't read the all the posts carefully in this thread since my last but I have to concur with Herman. For us to simply accept that you need to delete this column is for us to abandon you to perpetuating blatantly wrong practices. As suggested, if Access cannot do what you need to do without filthy hacks then the solution is to not use Access. If you need the features of a real database then you should use a real database. While there are others, SQL Server is the obvious choice. The Express edition is free, integrates into Visual Studio and can handle databases up to 10 GB in size. Migrating an already-deployed application to a full edition is also a simple matter of changing a connection string in a config file. Do yourself a favour.

 

[Herman]

As a case and point, just because i had to repair one today, here is what can happen with a 200MB Access database in a multi-user environment:

Healthy chunk of data in table GLDet:
http://www.freeimagehosting.net/9gufy

Same table, but really badly corrupted chunk:
http://www.freeimagehosting.net/andpb

In this particular case there was nothing i could do to repair the damage, the client will have to restore a backup from before all this, or start a new database and import stuff from this one.