• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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.
0
SpaceCoastLife
Asked:
SpaceCoastLife
  • 8
  • 6
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Replication ID is a GUID:  Globally Unique Identifier.  Beyond that, I'm not sure exactly what your question is.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SpaceCoastLifeAuthor Commented:
I don't want to get rid of the field, only change field size property to text. Can I do that?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
SpaceCoastLifeAuthor Commented:
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?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

<<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.
0
 
SpaceCoastLifeAuthor Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
SpaceCoastLifeAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< 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.
0
 
SpaceCoastLifeAuthor Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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).>>

  OK so then why not just import from SQL Server, let the uniqueidentifer come in as a replicationID, and leave your clientID as a long, and then assign a value to that after import?

  Sorry for being dense, but I'm just not sure I'm understanding the total scope of where your trying to get to.

  ReplicationID alone gives you a unique key and I'm not sure where your clientID comes in.

Jim.
0
 
SpaceCoastLifeAuthor Commented:
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!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now