|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
moving from Access to SQL 7.0Hi all
could anyone pleeeeeeeeeeease help I have big problems moving my database from Access to SQL 7.0... I had an Access database having queries, forms, and reports linked to another Access database having tables. Now, I'm moving my tables to SQL and I did thanks to Access wizard and everything is fine I have my tables in SQL. Now the problem is how to link the first Access mdb to SQL and keep tables relationships as they were.I went to File-->Get External Data-->Link Tables. and I linked them to SQL....the problem is that under Access When I was filling a form, before saving my entry, an autonumber was generated (primary key)...I automatically had access to some stuff....Now that my tables are linked to SQL, no autonumber is generated till the end (till I click on save) and I don't want to change the whole code and program I want to keep almost things as they were. could anyone help thanks alami_mari***@hotmail.com wrote on 17 Mar 2006 03:06:56 -0800:
Show quote > Hi all I'm pretty sure you're going to have to make a change. Access and SQL Server > could anyone pleeeeeeeeeeease help I have big problems moving my > database from Access to SQL 7.0... > I had an Access database having queries, forms, and reports linked to > another Access database having tables. Now, I'm moving my tables to SQL > and I did thanks to Access wizard and everything is fine I have my > tables in SQL. > Now the problem is how to link the first Access mdb to SQL and keep > tables relationships as they were.I went to File-->Get External > Data-->Link Tables. and I linked them to SQL....the problem is that > under Access When I was filling a form, before saving my entry, an > autonumber was generated (primary key)...I automatically had access to > some stuff....Now that my tables are linked to SQL, no autonumber is > generated till the end (till I click on save) and I don't want to > change the whole code and program I want to keep almost things as they > were. > could anyone help are 2 very different beasts. In Access when you add a new row to a table with an autonumber column you immediately get the next value. In SQL Server you don't get the value until the row has been committed to the transaction log. Dan so I have to change my whole code behind?
I noticed what you said meaning I don't get the value until I save what I did but this means that I'll have to change everything.There is no simple way to generate an autonumber before saving my transaction?this way I'll add few lines in my codes and keep all the rest.Else, I'll have to change everything :s Here is what happens: I have a table BLA (Delivery Bill) linked to a table SBLA.I have a form (using BLA) including a subform (using SBLA). I fill data in my form (data are added to BLA no problem) then I click on Quantity (this is part of the subform meaning it fills the SBLA table) and normally an autonumber should be generated and a line should be added automatically to SBLA table and this autonumber will be used for all the rest to generate some tables ...Now, with SQL, the autonumber is generated till I save meaning I don't go forward anymore.I can send you snapshots of my form to have a visual idea of what I'm talking about. Thanks a lot If you need to know a number now just insert a record on form create then
get the number. When you save you would use the update syntax. Either way, you got some work to do. <alami_mari***@hotmail.com> wrote in message Show quote news:1142596000.088235.119960@u72g2000cwu.googlegroups.com... > so I have to change my whole code behind? > I noticed what you said meaning I don't get the value until I save what > I did but this means that I'll have to change everything.There is no > simple way to generate an autonumber before saving my transaction?this > way I'll add few lines in my codes and keep all the rest.Else, I'll > have to change everything :s > Here is what happens: I have a table BLA (Delivery Bill) linked to a > table SBLA.I have a form (using BLA) including a subform (using SBLA). > I fill data in my form (data are added to BLA no problem) then I click > on Quantity (this is part of the subform meaning it fills the SBLA > table) and normally an autonumber should be generated and a line should > be added automatically to SBLA table and this autonumber will be used > for all the rest to generate some tables ...Now, with SQL, the > autonumber is generated till I save meaning I don't go forward > anymore.I can send you snapshots of my form to have a visual idea of > what I'm talking about. > Thanks a lot > I'll give a better image of what I'm doing and the problem I faced and
what I need: I have a form having the Order number, order date, client code...this information is taken from table BLA.on the same form,I've a subform having quantity bought,label of what has been bought,unit price,and total price.This information is taken from table SBLA.NOW under Access,I fill the order number, order date,client code and once I enter quantity bought a row is automatically added in SBLA with an auto number (primary key) and at the same moment a table is opened to show the arrivals to pick up what I need.WITH SQL,no autonumber is generated and no row is automatically added meaning the table arrival is not shown but once I click on save the row is then added but unfortunately I have no more access to the table arrival...Please keep in mind that I've a lot of code behind that manages all these procedures.Could anyone help pleeaaaaaaaase I decided to migrate to SQL to make my life easy since Access and SQL are both microsoft products BUT .... thanks a lot Like I said, either way you have a lot of work to do. MS SQL does have auto
number column but it is called identity. In Access you were able to use data bound controls to connect to the table and you did not need the save button because the work was done real time. You cannot do that with SQL server so you will need to add a save button and add all the information when the user clicks on it. I went through the same thing and I had to rewrite the codes but it was well worth it. <alami_mari***@hotmail.com> wrote in message Show quote news:1142636402.215699.304990@i39g2000cwa.googlegroups.com... > I'll give a better image of what I'm doing and the problem I faced and > what I need: > I have a form having the Order number, order date, client code...this > information is taken from table BLA.on the same form,I've a subform > having quantity bought,label of what has been bought,unit price,and > total price.This information is taken from table SBLA.NOW under > Access,I fill the order number, order date,client code and once I enter > quantity bought a row is automatically added in SBLA with an auto > number (primary key) and at the same moment a table is opened to show > the arrivals to pick up what I need.WITH SQL,no autonumber is generated > and no row is automatically added meaning the table arrival is not > shown but once I click on save the row is then added but unfortunately > I have no more access to the table arrival...Please keep in mind that > I've a lot of code behind that manages all these procedures.Could > anyone help pleeaaaaaaaase I decided to migrate to SQL to make my life > easy since Access and SQL are both microsoft products BUT .... > thanks a lot >  |
|||||||||||||||||||||||
Other interesting topics