Home--> Articles -->Site Tools
Post By 1234654 on 2008-9-26 15:15:08 [Reads:294]
Migrate an Access basis toward SQL Server
When one approaches ASP, it is frequent to resort in Access to learn to interface a web site appropriately with a data base. But very quickly, if the once published site knows success a little bit, Access shows its limits, and a more reliable SGBD proves to be necessary. Let's see here how to transfer an Access basis in a basis SQL Server.
Fundamental differences
To the difference of Access, SQL Server requires the user's identification, that he is administrator of the data bases or owner of one among them, or, and it is not the least case, that he is user of it, that means that he reaches the data contained for example through one ASP page. In a DSN context / ODBC, the call of the data base will make itself therefore by the name of the DSN, but assorted of a name of user assorted of a password, this couple of identifying be having to declared previously in the users SQL Server side server (it is administrator's NT to make it).
In the global.asa, we will have a call of this type therefore:
Sub Application On_Start Application ("ConnectionString") = "DSN=nomdudsn;User Id=user;PASSWORD=password"
and, if one calls the connection to every page, one will have then:
Conn.Open "nomdudsn", "to use", "password",
The second fundamental difference between the two SGBD resides in the fact that the types of fields don't carry the same names: a field memo becomes ntext, a numeric field becomes int. As for the equivalent of the field of NuméroAuto type, it is in fact about a field int, but with the activated option Meter. Not to forget, at the time of the migration, to nick this slot every time necessary.
Methods of migration
The first stage consists in creating on your machine a DSN that will allow you to reach your basis SQL Server while identifying you as owner of the basis.
If you have the Desktop edition of SQL Server 7, and if you arrange to the minimum of the right db_owner (owner of the basis), the simplest is to import some Access data from the helper of DTS import. For it, click on your basis and select to Import some data. Select the type of data source (Access), select the basis containing the data to import, identify yourselves then as owner of the basis in the following page, while using the identification SQL Server.
The helper is going to propose to you, table by table and as list, to transform the data in view of their import. It is here the most important point of the operation; if most parameters of fields will be recognized without difficulty, it agrees to conduct the following verifications:
1 to nick the slot Meter for all fields (1 by table) of NuméroAuto type, and to verify that the Null #118alue is forbidden.
2 well to double check that the fields memo has been transformed in fields ntext. In the contrary case, to conduct this modification, then to place all fields ntext at the end of table, with the help of a simple to cut / to glue.
You can now finish the import, an indicator of progression will show off.
If you don't have the Deskpro edition of SQL Server 7, use the helper of migration of Access 2000, who is located in Tools / Utilitarian of data bases / Helper of migration SQL Server. Verify then by your hébergeur that the fields meter has been parameterized well and that the fields ntext is placed at the end of every table.
Side ASP pages
Except the call of the connection that has been evoked higher, he/it agrees to note that an important modification is to proceed in your ASP pages. The fields ntext is, there also, to display in last. What means that, in an example where, after a field ntext, you must display one date or a field for example normal text, it agrees to stock these in variables and to display the variable and no the content of the field:
signature = rs ("Signature_Article") 'then one displays everything that can be displayed, until the field included ntext = signature 'instead of = rs ("Signature_Article")
With these few instructions, you have information of basis, but indispensable, necessary to a passage easy of a platform limited to a really effective SGBD.
Migrate an Access basis toward SQL Server
Post By 1234654 on 2008-9-26 15:15:08 [Reads:294]
Migrate an Access basis toward SQL Server
When one approaches ASP, it is frequent to resort in Access to learn to interface a web site appropriately with a data base. But very quickly, if the once published site knows success a little bit, Access shows its limits, and a more reliable SGBD proves to be necessary. Let's see here how to transfer an Access basis in a basis SQL Server.
Fundamental differences
To the difference of Access, SQL Server requires the user's identification, that he is administrator of the data bases or owner of one among them, or, and it is not the least case, that he is user of it, that means that he reaches the data contained for example through one ASP page. In a DSN context / ODBC, the call of the data base will make itself therefore by the name of the DSN, but assorted of a name of user assorted of a password, this couple of identifying be having to declared previously in the users SQL Server side server (it is administrator's NT to make it).
In the global.asa, we will have a call of this type therefore:
Sub Application On_Start Application ("ConnectionString") = "DSN=nomdudsn;User Id=user;PASSWORD=password"
and, if one calls the connection to every page, one will have then:
Conn.Open "nomdudsn", "to use", "password",
The second fundamental difference between the two SGBD resides in the fact that the types of fields don't carry the same names: a field memo becomes ntext, a numeric field becomes int. As for the equivalent of the field of NuméroAuto type, it is in fact about a field int, but with the activated option Meter. Not to forget, at the time of the migration, to nick this slot every time necessary.
Methods of migration
The first stage consists in creating on your machine a DSN that will allow you to reach your basis SQL Server while identifying you as owner of the basis.
If you have the Desktop edition of SQL Server 7, and if you arrange to the minimum of the right db_owner (owner of the basis), the simplest is to import some Access data from the helper of DTS import. For it, click on your basis and select to Import some data. Select the type of data source (Access), select the basis containing the data to import, identify yourselves then as owner of the basis in the following page, while using the identification SQL Server.
The helper is going to propose to you, table by table and as list, to transform the data in view of their import. It is here the most important point of the operation; if most parameters of fields will be recognized without difficulty, it agrees to conduct the following verifications:
1 to nick the slot Meter for all fields (1 by table) of NuméroAuto type, and to verify that the Null #118alue is forbidden.
2 well to double check that the fields memo has been transformed in fields ntext. In the contrary case, to conduct this modification, then to place all fields ntext at the end of table, with the help of a simple to cut / to glue.
You can now finish the import, an indicator of progression will show off.
If you don't have the Deskpro edition of SQL Server 7, use the helper of migration of Access 2000, who is located in Tools / Utilitarian of data bases / Helper of migration SQL Server. Verify then by your hébergeur that the fields meter has been parameterized well and that the fields ntext is placed at the end of every table.
Side ASP pages
Except the call of the connection that has been evoked higher, he/it agrees to note that an important modification is to proceed in your ASP pages. The fields ntext is, there also, to display in last. What means that, in an example where, after a field ntext, you must display one date or a field for example normal text, it agrees to stock these in variables and to display the variable and no the content of the field:
signature = rs ("Signature_Article") 'then one displays everything that can be displayed, until the field included ntext = signature 'instead of = rs ("Signature_Article")
With these few instructions, you have information of basis, but indispensable, necessary to a passage easy of a platform limited to a really effective SGBD.