SpaceCoastLife
asked on
How do you change the Field Size property in Access 2002
I'm attempting to import data from a SQL Server database into Access 2002. It exports out of SQL Server just fine except the Field Size property of all the keyed fields is "Replication ID" - not a term I'm familiar with. Once it's in Access, you cannot change the property if there are records in the table. Neither can you run an INSERT query into a new table with identical fields if the new table doesn't also have Replication ID in the same fields. All of the records will transfer except for those fields. For example, if the source table has a field named "CLIENT" whose field size is set to Replication ID and the new table - also with a field named "CLIENT" has it's field size property set to anything but Replication ID, that column in the new table will be blank.
This is a critical project I need a resolution to and any Expert with knowledge in this area sharing his/her experience will be greatly appreciated.
This is a critical project I need a resolution to and any Expert with knowledge in this area sharing his/her experience will be greatly appreciated.
FYI if your question is, how do I get rid of that field, you can use this:
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=11#11
Jim.
You might also want to try this:
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7
Depending on what your needs are. My guess is you would only need the first so you can deal with the fields themselves. The second is for entire DB's that have been replicated.
Jim.
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7
Depending on what your needs are. My guess is you would only need the first so you can deal with the fields themselves. The second is for entire DB's that have been replicated.
Jim.
ASKER
I don't want to get rid of the field, only change field size property to text. Can I do that?
<<I don't want to get rid of the field, only change field size property to text. Can I do that? >>
Not that I'm aware of, although Micrsoft made a lot of changes in the ability of DML statements. I vaugly remember someone saying that you could now change the field type, but I don't know if that was a replication ID field.
In the past, you'd have to create a new column with the correct type, then copy the values in, and finially delete the old column.
I just checked and the article here:
http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx#acintsql_alttable
and it say's that you can do:
ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
now. But I didn't know that it will work on a replication ID, which is a special field type. You may have to run that one utility to remove the system attribute, then use the SQL DML statement above.
Sorry I can't be more definitive, but it's not often that one tries to change a field type on the fly.
Jim.
Not that I'm aware of, although Micrsoft made a lot of changes in the ability of DML statements. I vaugly remember someone saying that you could now change the field type, but I don't know if that was a replication ID field.
In the past, you'd have to create a new column with the correct type, then copy the values in, and finially delete the old column.
I just checked and the article here:
http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx#acintsql_alttable
and it say's that you can do:
ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
now. But I didn't know that it will work on a replication ID, which is a special field type. You may have to run that one utility to remove the system attribute, then use the SQL DML statement above.
Sorry I can't be more definitive, but it's not often that one tries to change a field type on the fly.
Jim.
ASKER
It doesn't. Do you know how to set the Column Properties of a field in an empty table in SQL Server 2005 Studio Manager such that when the table is exported into Access 2002 it will come in with a Field Size of Replication ID?
<<Do you know how to set the Column Properties of a field in an empty table in SQL Server 2005 Studio Manager such that when the table is exported into Access 2002 it will come in with a Field Size of Replication ID? >>
Set the field type to uniqueidentifier.
Jim.
ASKER
I tried that but I get the message: "Conversion from int to uniqueidentifier is not supported on the connected database server".
Can't say I understand what it means, however.
Can't say I understand what it means, however.
You can't do it. I assume were talking about the SQL server DB? I thought you wanted to move away from the Replication ID?
in anycase, you can't convert an int to a uniqueidentifier. A uniqueidentifier in SQL is a 128 bit GUID generated by SQL Server itself.
You'll need to create a new column, then delete the old one.
And I didn't think about that earlier in regards to Access, but I'm sure it's true for Access as well.
If you really do have control of the SQL Server table design, then I would stay away from the GUID's entirely.
Jim.
in anycase, you can't convert an int to a uniqueidentifier. A uniqueidentifier in SQL is a 128 bit GUID generated by SQL Server itself.
You'll need to create a new column, then delete the old one.
And I didn't think about that earlier in regards to Access, but I'm sure it's true for Access as well.
If you really do have control of the SQL Server table design, then I would stay away from the GUID's entirely.
Jim.
ASKER
This customer sent us their data in a SQL Server database we have to import into our SQL Server database. The mapping is quite complex so I export their data into Access. Then I link our empty SQL Server database to their data - now in Access. The problem I've run into is their ClientID number is a uniqueidentifier, Ours is Identity (Auto Num in Access). I have to import their ClientID into our database along with their data.
I was able to change the appropriate field in our database to uniqueidentifier by deleting the field and recreating it. Unfortunately, it doesn't solve the problem either. I'm guessing it's because you can't have 2 unique identifier fields in the same table (theirs and ours).
The values in their clientID field appears to be a combination of numbers, symbols and characters - probably 30-40 characters in all. I was hoping to set the field type that stores their ClientID in our database to Text but apparently you can't transfer a field designated uniqueidentifier to a text field. Why, I have no idea. Anyway, that's the problem I need to solve.
I was able to change the appropriate field in our database to uniqueidentifier by deleting the field and recreating it. Unfortunately, it doesn't solve the problem either. I'm guessing it's because you can't have 2 unique identifier fields in the same table (theirs and ours).
The values in their clientID field appears to be a combination of numbers, symbols and characters - probably 30-40 characters in all. I was hoping to set the field type that stores their ClientID in our database to Text but apparently you can't transfer a field designated uniqueidentifier to a text field. Why, I have no idea. Anyway, that's the problem I need to solve.
>>The values in their clientID field appears to be a combination of numbers, symbols and characters - probably 30-40 characters in all<<
They are hexadecimal characters 0-9 and A-F. They are 36 bytes in length. It is maintained in the SQL Server as a 16-byte value.
They are hexadecimal characters 0-9 and A-F. They are 36 bytes in length. It is maintained in the SQL Server as a 16-byte value.
<< I was hoping to set the field type that stores their ClientID in our database to Text but apparently you can't transfer a field designated uniqueidentifier to a text field. Why, I have no idea. Anyway, that's the problem I need to solve. >>
Sounds like you need to skip Access. But if you must use it as a go between, then I don't understand why you want to get rid of the replication ID in the tables in Access. Is it because of size? or just haveing to work with the data?
If the latter, you can add an additional column to the table, setup unique keys, and then work using those. Then exporting back out to SQL Server, just use the replication ID column.
Jim.
Sounds like you need to skip Access. But if you must use it as a go between, then I don't understand why you want to get rid of the replication ID in the tables in Access. Is it because of size? or just haveing to work with the data?
If the latter, you can add an additional column to the table, setup unique keys, and then work using those. Then exporting back out to SQL Server, just use the replication ID column.
Jim.
ASKER
I don't necessarily want to get rid of it but it's stopping me from importing theie number.
One of the requirements is I have to include their clientid data in our db. I have a "customerid" field in our db to store it and I can make it any data type I want but it doesn't seem to matter what it is because the values wont populate even if I make the field in our db a uniqueidentifier type. Nothing I've tried will allow me to import their number. Our master table also has a clientid field which is a unique auto number.I'm thinking that's why it won't come in when there are 2 unique identifier fields in the same table. What I don't get is why it won't transfer in if the field type is text.
One of the requirements is I have to include their clientid data in our db. I have a "customerid" field in our db to store it and I can make it any data type I want but it doesn't seem to matter what it is because the values wont populate even if I make the field in our db a uniqueidentifier type. Nothing I've tried will allow me to import their number. Our master table also has a clientid field which is a unique auto number.I'm thinking that's why it won't come in when there are 2 unique identifier fields in the same table. What I don't get is why it won't transfer in if the field type is text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My theory of why their id wouldn't come in was wrong. It actually had nothing to do with what we are talking about. So in summary, if I use the uniqueidentifier data type in our customerid field it now transfers properly. Sorry for all the confusion and thanks for the education!
Replication ID is a GUID: Globally Unique Identifier. Beyond that, I'm not sure exactly what your question is.
Jim.