Changing Data Type Once DB populated

[economyman]

Hi There

I have a DB with several thousand records in a table representing SIM card information. One of the fields is a FK Customer field. It is based on a lookup from the Customer table however for some reason unbeknown to me the data type created was Text instead of Number, even though the bound column is an AutoNumber field (which is not displayed). Somehow I need to change this because it is causing all kinds of problems in managing the DB and its functionality.

When I remove the relationship and try to change the Data Type or use Acccess Lookup Wizard, neither work and all generate errors when trying to save the table.

Any ideas on the best approach to resolve this?

Thank you and sorry for the rookie question.

 

[MSAccessRookie]

Hi There

I have a DB with several thousand records in a table representing SIM card information. One of the fields is a FK Customer field. It is based on a lookup from the Customer table however for some reason unbeknown to me the data type created was Text instead of Number, even though the bound column is an AutoNumber field (which is not displayed). Somehow I need to change this because it is causing all kinds of problems in managing the DB and its functionality.

When I remove the relationship and try to change the Data Type or use Acccess Lookup Wizard, neither work and all generate errors when trying to save the table.

Any ideas on the best approach to resolve this?

Thank you and sorry for the rookie question.

Greetings Fellow Rookie.

Changing the type of the field outright is not the best idea, but what you might want to think about doing, is to create a new column of the correct type, and migrate the original column to the new one. Once you verify that all records migrate successfully, you can rename both columns, making the new one have te same name as the old one, and saving the original to give you a fallback position, and then test your application with the newly created column.

I hope I said this in a way that was easy to understand

 

[datAdrenaline]

... the way I alter my tables after they are added to the TableDef collection is by executing an ALTER TABLE SQL statement through ADO (CurrentProject.Connection.Execute). The reason for useing ADO is that ADO fully supports JET 4.0 syntax, so it has more capability than DAO (CurrentDb.Execute) ... Although in this particular circumstance, either object model is capable of the performing the requested action.

For your case, the SQL statement syntax would look something like this:

ALTER TABLE tblsometable ALTER COLUMN somefield INTEGER

{note: lower case denotes user values need to be used in that place, also the INTEGER JET datatype is a 4 byte integer which is equivalent to Access's Number/Long Integer}

Translating that to code, you would do something like ...

Function FooBar()
    
    Dim strSQL As String
     
    strSQL = "ALTER TABLE tblsometable ALTER COLUMN somefield INTEGER"
    CurrentProject.Connection.Execute strSQL, , adCmdText
    
End Function

Also ... I do understand MSAccessRookies reservations about changing the datatype. But, in this case there should not be any issues. I personally would just make a back up of the datafile, then change the datatype, if "bad things happen", you have the backup...

....

Also do take note that, you can just change it in the Table Design UI, which is by far the simplest way ... but again, I would just make a back up, then do the change ... and restore the back up if bad things happen.

 

[MSAccessRookie]

... the way I alter my tables after they are added to the TableDef collection is by executing an ALTER TABLE SQL statement through ADO (CurrentProject.Connection.Execute). The reason for useing ADO is that ADO fully supports JET 4.0 syntax, so it has more capability than DAO (CurrentDb.Execute) ... Although in this particular circumstance, either object model is capable of the performing the requested action.

For your case, the SQL statement syntax would look something like this:

ALTER TABLE tblsometable ALTER COLUMN somefield INTEGER

{note: lower case denotes user values need to be used in that place, also the INTEGER JET datatype is a 4 byte integer which is equivalent to Access's Number/Long Integer}

Translating that to code, you would do something like ...

Function FooBar()
 
    Dim strSQL As String
 
    strSQL = "ALTER TABLE tblsometable ALTER COLUMN somefield INTEGER"
    CurrentProject.Connection.Execute strSQL, , adCmdText
 
End Function

Also ... I do understand MSAccessRookies reservations about changing the datatype. But, in this case there should not be any issues. I personally would just make a back up of the datafile, then change the datatype, if "bad things happen", you have the backup...

....

Also do take note that, you can just change it in the Table Design UI, which is by far the simplest way ... but again, I would just make a back up, then do the change ... and restore the back up if bad things happen.

My reservations stem from the fact that a long time ago (Pre-Access), this was sometimes the only way to do it. Besides, an improper field type could have improper data stored in it.

 

[datAdrenaline]

>> improper field type could have improper data stored in it <<

Very true ... and in the case going from a text to a numeric, The value will be coerced to 0 ... which could indeed cause some issues if the field is participating in a enforced Relationship. But in this case where the field is populated via a selection from a foreign table, I am guessing the data to be fairly clean. But if one would inadvertently choose say a OLE Object datatype, your data will be removed ... with out warning!! ... fun eh?

We both agree that precautions should be taken in order to prevent data loss, which should send a clear message that "bad stuff" can happen at the most inopportune times!! ...

Also, please know that I hope my post did not send off a message that your technique is incorrect, as it is definately not ... I was simply providing additional techniques that the OP may find advantageous.

 

[economyman]

MSAccessRookie's proposal is the perfect solution for me - I should have thought of that myself but there you go. You are a more experienced rookie then me.

Changing the datatype as datAdrenaline suggests is what I tried before starting this thread - it didn't work, which is not surprising considering what a mess I (with some help from Access) managed to make of the field. In fact it should have always been a Number data type and in practical terms it contained pure text in most cases, the text being the name of the customer rather than it's ID, and only in a few cases did the field contain the customer ID. How did that happen - my fault - when using update queries to update the data which I do regularly due to the business model at hand, I updated the customer with their name instead of ID. Had the field been correctly structured from the beginning though, this would not have been possible as a type mismatch error would have stopped me.

Truth is I set up this table several months ago when I had just got into learning Access and I managed to make a mess of it because of a mix-up with how lookups work.

Anyway, now using an update query it has been easy to insert into another field with the correct data type the correct values and restructure it. Anything else would have been too time consuming so thank you MSAccessRookie.

 

[datAdrenaline]

Excellent! ... I am glad to hear the solution provided by MSAccessRookie was what you needed! ...

It does sound like a mess that you're in! But it is encouraging that you can see a way through it! ...

 

[neileg]

I'm worried that you have a table level lookup. I've never understood why MS thought these were a good idea. In anything other than the most basic of situations, these lookups cause more trouble than they are worth. It is infinitely preferable to use a form with a combo box or a list box as a lookup.

 

[economyman]

I'm worried that you have a table level lookup. I've never understood why MS thought these were a good idea. In anything other than the most basic of situations, these lookups cause more trouble than they are worth. It is infinitely preferable to use a form with a combo box or a list box as a lookup.

Indeed it is a a table level lookup. As I said, I set up this DB when I was just starting off with Access. In newer databases I have not done this but the truth is that at the moment I am only using this DB in its raw format without forms. I write queries including action queries and then remove them. No one else is using it right now but that will change.

I'd be interested in understanding the reasoning behind not using table level lookups.

Thanks

 

[boblarson]

I'd be interested in understanding the reasoning behind not using table level lookups.

See here for why:
http://www.mvps.org/access/lookupfields.htm