i have a mySQL db that i need to transfer to Access everything goes well but when I put the DB to work, the AutoNumber does not work and it throws my website off what can i do so i can transfer it or convert the db to be exactly as how it was with the AutoNumber and everything... i tried changing the property to auto number but it does not allow me to do it because there is data in the table allready.
help please ASAP i need to figure this out or i get Fired... really
Assuming you're current field is called "OriginalKeyField", and OriginalKeyField is the primary key on the table you're working with called "tblMyTable":
Create new autonumber field called "KeyField"
Temporarily drop your relationships and update any foreign key references to OriginalKeyField to hold the value of KeyField. For example, if tblMyOtherTable references tblMyTable where tblMyOtherTable.OriginalKeyField = tblMyTable.OriginalKeyField, you want to say: UPDATE tblMyOtherTable SET OriginalKeyField = KeyField FROM tblMyTable WHERE tblMyTable.OriginalKeyField = tblMyOtherTable.OriginalKeyField
Drop OriginalKeyField (this is optional if for some reason you want to retain the initial value before you started mucking around with it)
Rename KeyField to OriginalKeyField
Set the "new" OriginalKeyField to be your PK
*** As with every situation where you'll be messing with re-keying tables, make SURE you backup the whole database before you begin ***
ok i tried understanding but i didnt... i dont have any relationships set up.. this is what this table it maybe if you understand it a little more you will be able to help me..
this table is a table where i have all the categories for the products there is a filed called 'idCategory' ,which is an Auto Number and primary key, and over the years had been added and subtracted categories so it does not count 1,2,3 anymore its skips arround because there are some Numbers that where deleted when the category was removed.. now when i transfer the db to access the filed 'idCategory' does not stay as AutoNumber or primary key
therefore when i try to make it work in the website and add a category it wont work because it is not adding a number at all
so how can i get it to transfer with the auto number or how can i override access so i can set that to auto number?
Woah now... Are you trying to drive a website with Access when you already had it in MySQL? What's the business justification for this?
The plain english description of what I was suggesting is a lot simpler than it may look. The problem is (as you have noticed) you can't designate an existing column as an autonumber. This obviously means you have to create a NEW column if you'd like an autonumber on your table. Once you've created the new column, you can just delete the old one and rename the fresh column to idCategory or whatever it is.
The challenge comes in to play if you need to preserve the "old" values in idCategory. That's where the whole updating foreign key references and such was going. If you don't care about the old values, then none of that extra updating stuff matters.
i know its a stupid move driving it of an access but its the only option i have.. i didnt make this site its a CMS called candy press and it was working in an old server with a mySQL but when I put it on the new server it wont work with the MySQL i get a message saying
ADODB.Recordset error '800a0cb3'
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.
/velocitynet/scripts/prodList.asp, line 479
and i dont know how to solve it
i have gone around that codeing in the page a million times there is nothing wrong with it and i was working with the other server on a mysql fine i dont understand what the problem is.. help me
if you can help me that would be so cool if you can call me and we can do it over the phone it would be better but thats if you can
The error you're receiving is telling you the recordset is not updateable. Google around for "updaeable ADODB recordset" and you should get a bunch of stuff.
The two most common culprits are not using a client side CursorType (which it's telling you about) and not having a primary key defined on your table. Given that it's yelling about bookmarks, I'd put my money on the CursorType.
Switching the back end all-together is swatting at flies with a sledgehammer.
i cant find anything at all... if i give you access to the FTP tell you where the page is do you think you can fix it... the problem is that i didnt make this page its just confusing i know basic asp not a lot so thats why its a little confusing...