Access & SQL

<i><b>Originally posted by : loiler (wivina_lo@impiric.com.hk)</b></i><br /><br />In my asp files, initally I was using a Microsoft Access database file for searching and editing records, but when many users try to edit the database at the same time, it crashed, so now I will have to switch to SQL. Is it possible to import an Access mdb to a SQL database? And what are the steps? Can anybody show me show useful websites in creating the SQL server as well as the other steps for switching to the SQL?

<i><b>Originally posted by : Broxi (bigcthomson@hotmail.com)</b></i><br />Yes it is possible to import an access database into SQL Enterprise Manager - What version of SQL are you using?<br /><br /><br />------------<br />loiler at 3/23/2001 3:10:06 AM<br /><br /><br />In my asp files, initally I was using a Microsoft Access database file for searching and editing records, but when many users try to edit the database at the same time, it crashed, so now I will have to switch to SQL. Is it possible to import an Access mdb to a SQL database? And what are the steps? Can anybody show me show useful websites in creating the SQL server as well as the other steps for switching to the SQL?

<i><b>Originally posted by : loiler (wivina_lo@impiric.com.hk)</b></i><br />Thanks, I just successfully imported the Access database to a SQL database, but now I ran into another problem, SQL doesn't support the moveLast method and I can't make it to go to the end when I want to add a new record, also, I got the following error message:<br /><br />ADODB.Recordset error '800a0cb3' <br /><br />The operation requested by the application is not supported by the provider. <br /><br />and that line is where I put my AddNew command.<br /><br />if oRs.recordcount <> 0 then<br />' oRs.movelast<br /> ult = oRs("code") + 1<br />else<br /> ult = 1 <br />end if<br /> oRs.Addnew<br /> oRs("Code") = ult<br /> oRs("reference") = ref<br /> oRs("name") = name<br /> oRs("email") = email<br /> oRs("topic") = topic<br /> oRs("message") = message<br /> oRs("PostDate") = date<br /> oRs.update<br /><br />I already commented out the moveLast command.<br /> <br /><br />------------<br />Broxi at 3/23/2001 6:47:24 AM<br /><br />Yes it is possible to import an access database into SQL Enterprise Manager - What version of SQL are you using?<br /><br />

<i><b>Originally posted by : Broxi (bigcthomson@hotmail.com)</b></i><br />Is it an Update statement you really wish to use or is it a simple insert into a database you need?<br /><br />Your code looks a little like an Insert apart from the update method...<br /><br />Are you trying to increment the number by 1 each time? if so there is an identity and identity seed and and increment, this is similar to autonumber in access, except you can increment by 1,2,3 etc.<br /><br />What actually do you wish to do?<br /><br /><br />------------<br />loiler at 3/23/2001 10:21:22 AM<br /><br />Thanks, I just successfully imported the Access database to a SQL database, but now I ran into another problem, SQL doesn't support the moveLast method and I can't make it to go to the end when I want to add a new record, also, I got the following error message:<br /><br />ADODB.Recordset error '800a0cb3' <br /><br />The operation requested by the application is not supported by the provider. <br /><br />and that line is where I put my AddNew command.<br /><br />if oRs.recordcount <> 0 then<br />' oRs.movelast<br /> ult = oRs("code") + 1<br />else<br /> ult = 1 <br />end if<br /> oRs.Addnew<br /> oRs("Code") = ult<br /> oRs("reference") = ref<br /> oRs("name") = name<br /> oRs("email") = email<br /> oRs("topic") = topic<br /> oRs("message") = message<br /> oRs("PostDate") = date<br /> oRs.update<br /><br />I already commented out the moveLast command.<br /> <br /><br />------------<br />Broxi at 3/23/2001 6:47:24 AM<br /><br />Yes it is possible to import an access database into SQL Enterprise Manager - What version of SQL are you using?<br /><br />

<i><b>Originally posted by : loiler (wivina_lo@impiric.com.hk)</b></i><br />I need to add a new record to it... with all those fields like name, email, topic... and I increase the "Code" field by one because like u said, I want to make it to work like an autonumber, but does SQL support AddNew method? If not, how can I add a new records with all those fields to it?<br /><br /><br />------------<br />Broxi at 3/23/2001 10:32:55 AM<br /><br />Is it an Update statement you really wish to use or is it a simple insert into a database you need?<br /><br />Your code looks a little like an Insert apart from the update method...<br /><br />Are you trying to increment the number by 1 each time? if so there is an identity and identity seed and and increment, this is similar to autonumber in access, except you can increment by 1,2,3 etc.<br /><br />What actually do you wish to do?<br /><br /><br />------------<br />loiler at 3/23/2001 10:21:22 AM<br /><br />Thanks, I just successfully imported the Access database to a SQL database, but now I ran into another problem, SQL doesn't support the moveLast method and I can't make it to go to the end when I want to add a new record, also, I got the following error message:<br /><br />ADODB.Recordset error '800a0cb3' <br /><br />The operation requested by the application is not supported by the provider. <br /><br />and that line is where I put my AddNew command.<br /><br />if oRs.recordcount <> 0 then<br />' oRs.movelast<br /> ult = oRs("code") + 1<br />else<br /> ult = 1 <br />end if<br /> oRs.Addnew<br /> oRs("Code") = ult<br /> oRs("reference") = ref<br /> oRs("name") = name<br /> oRs("email") = email<br /> oRs("topic") = topic<br /> oRs("message") = message<br /> oRs("PostDate") = date<br /> oRs.update<br /><br />I already commented out the moveLast command.<br /> <br /><br />------------<br />Broxi at 3/23/2001 6:47:24 AM<br /><br />Yes it is possible to import an access database into SQL Enterprise Manager - What version of SQL are you using?<br /><br />

<i><b>Originally posted by : Broxi (bigcthomson@hotmail.com)</b></i><br />Try something like this - where I have written etc substitute the other colum names<br /><br />Request Querystring - gets the values fro the querystring coming from the get action of a form - you could also do Request("Code") as shorthand<br /><br />The Text Boxs in the Form should have the same values as the column names - method get and call the script.<br /><br />You only need an autonumber if you want to make the fields unique..<br /><br />Happy ASP -ing<br />----<br /><br /><% @Language = "VBScript" %><br /><%<br />Option Explicit<br />response.buffer = true<br />response.expires = -1000 <br />%><br /><br /><!--#include file="../adovbs.inc" --><br />(Database Include File which contains the User Name and Password and Connection -cn)<br /><!--#include file="../include/database.inc" --><br /><br /><%<br />dim cn<br />dim strSQL<br />dim cmd<br /><br />set cn=Server.CreateObject ("ADODB.Connection")<br />cn.open strConn<br /><br />set cmd = Server.CreateObject ("ADODB.Command")<br />cmd.ActiveConnection = cn<br />cmd.CommandType = adCmdText<br />strSQL = "INSERT INTO tblUserGroups (Code,Reference,Name, etc) VALUES ('" & request.querystring("Code") & "','" & session("Reference") & "'," & request.querystring("Name") & ")" etc<br />cmd.CommandText = strSQL<br />cmd.Execute<br />set cmd = nothing<br />cn.Close<br />set cn = nothing<br /><br />----<br /><br /><br />------------<br />loiler at 3/23/2001 10:51:53 AM<br /><br />I need to add a new record to it... with all those fields like name, email, topic... and I increase the "Code" field by one because like u said, I want to make it to work like an autonumber, but does SQL support AddNew method? If not, how can I add a new records with all those fields to it?<br /><br /><br />------------<br />Broxi at 3/23/2001 10:32:55 AM<br /><br />Is it an Update statement you really wish to use or is it a simple insert into a database you need?<br /><br />Your code looks a little like an Insert apart from the update method...<br /><br />Are you trying to increment the number by 1 each time? if so there is an identity and identity seed and and increment, this is similar to autonumber in access, except you can increment by 1,2,3 etc.<br /><br />What actually do you wish to do?<br /><br /><br />------------<br />loiler at 3/23/2001 10:21:22 AM<br /><br />Thanks, I just successfully imported the Access database to a SQL database, but now I ran into another problem, SQL doesn't support the moveLast method and I can't make it to go to the end when I want to add a new record, also, I got the following error message:<br /><br />ADODB.Recordset error '800a0cb3' <br /><br />The operation requested by the application is not supported by the provider. <br /><br />and that line is where I put my AddNew command.<br /><br />if oRs.recordcount <> 0 then<br />' oRs.movelast<br /> ult = oRs("code") + 1<br />else<br /> ult = 1 <br />end if<br /> oRs.Addnew<br /> oRs("Code") = ult<br /> oRs("reference") = ref<br /> oRs("name") = name<br /> oRs("email") = email<br /> oRs("topic") = topic<br /> oRs("message") = message<br /> oRs("PostDate") = date<br /> oRs.update<br /><br />I already commented out the moveLast command.<br /> <br /><br />------------<br />Broxi at 3/23/2001 6:47:24 AM<br /><br />Yes it is possible to import an access database into SQL Enterprise Manager - What version of SQL are you using?<br /><br />

<i><b>Originally posted by : loiler (wivina_lo@impiric.com.hk)</b></i><br />Thanks a lot for your help, I did it!<br /><br />I have another question.... sorry... <br />Is it possible to delete a record from the database?

<i><b>Originally posted by : steve schofield (steve@aspfree.com)</b></i><br /><br />http://aspfree.com/asp/delete1.asp<br /><br /><br />------------<br />loiler at 3/24/2001 2:02:26 AM<br /><br />Thanks a lot for your help, I did it!<br /><br />I have another question.... sorry... <br />Is it possible to delete a record from the database?