Access -> MS SQL *ASP*

Ok, here we go.

1) Where did you hear this? Using the ADO RecordCount method works fine as long as you use the right kind of cursor. You can't use SELECT COUNT(*) with anything other than another aggregate function. If you're using SQL Server then you could use a stored procedure that checks the count and assigns it to an output variable, and then do your regular select statement.

2) The easiest way to transfer the database is to import it using Enterprise Manager. Just get a free demo of SQL Server 2000 and install the client tools only.

3) The syntax for creating a table:
CREATE TABLE TableName
(
PrimaryField smallint IDENTITY (1, 1) PRIMARY KEY,
AnotherField varchar(50) NOT NULL
)
Do yourself a favor and get Enterprise Manager!

4) You should be able to import your Access db without to many problems. After you've imported check your primary keys/seeds, sometimes those aren't setup right when the database is imported.

Thank you, however there are still a couple of questions left:

1) What do you mean by putting the curser in the right place? DO you mean that I should make sure to use OBJ.movelast, or...?
You say aggregate function, what is that? What does it mean? Is it posible to select and count in the same SQL sentence?

2) I don't know if my server have that installed.. hope so Think so actually!

3) If it have Enterprice Manager installed, I guess it aint needed... I have heard though that EM have problems with autonumerated fields, what is that called (INTEGER, MEMO etc... but the one for autonumerating)?

4) I hope it wont be a problem anyway

Thank you for this so far. I hope you can jelp with the final bit here!

Thanks
~Casper

1) You should specify the CursorType to make sure the RecordCount method is supported, have a look at this link for more info: http://www.asp101.com/samples/db_count.asp

An aggregate function is one like COUNT/AVG/SUM etc. As I said before you're best bet is to use a stored procedure, maybe like this:

CREATE PROCEDURE Owner.ProcedureName
@Records integer OUTPUT

SELECT Fields, FROM Tables
SET @Records = @@ROWCOUNT

2) Good

3) By autonumerating do you mean Identity? An identity field is one that increments by a set integer automatically. This field is usually set as the primary key for the table. EM doesn't have any problems with Identity fields, when you create a new table just set the column you want to increment automatically to Identity:Yes and then set the Identity seed/increment, for example a seed of 1 with an increment of 1 will start at 1 and then add 1 for each new record.

Hi again.

I have found out that what my server provides is an MS SQL Server Database! Now, what is the difference here???

Can you explain what the difference is, and how to convert it? I found this http://www.mysql.com/doc/S/E/SEC583.html, but I don't know of mySQL and SQL server is the one and same thing...

Someone, please help me

Thanks!
~Casper

I thought that you wanted a MS SQL Server database? MS SQL & MySQL are not the same thing, to find out more abuot SQL Server you can check out microsoft.com/sql . If you're running ASP then I would probably stick with the MS SQL database, as they work pretty good together.

As I said above, the easiest way to import from Access to MS SQL is to use Enterprise Manager, it's really pretty easy, point and click stuff. If you can't install EM then maybe your host will import the database for you, but I strongly recommend getting EM, it will make your life much easier.

Hi again,

I didn't see it before yesterday, but aparently its the SQL Server that I can get from my server (fasthosts.co.uk)...

As all I have is hosting, I bet that I can't install anything on the servers...

I will have to create it all from the beginning...

Ok, I have to create the fields.
Can you verify if the syntax I write will give the right field-type?

Time/date = timestamp
number = integer
text field = varchar()
autonumerating = identify
large field = memo

How do I make a field so that it cannot have the same value twice in the table? I know it can be done with Access, but can it be done with SQL as well?
I hope so....

Thank you!
~Casper

PS: It is worth the efford to change teh database, right?

hmm, I think maybe you're a little confused, it's ok that's why DBAs get paid so much money

You shouldn't have to install anything on the server. If you've purchased a SQL account from your hosting company then it's just a matter of importing your database from Access to the SQL Server. They should have given you the SQL Server name or IP number. Just use Enterprise Manager (installed on your computer), register your new SQL Server (it will ask you for the name, this is where you enter the name or IP number, then it will ask you for the login credentials, it will probably be using SQL security). Once the server is registered then it's a simple right-click on the database name, select all tasks, then import data. You'll get another menu that asks you for the data source (which would be your Access database), then it will ask you for the data destination (which is your new SQL Server), then it will ask you if you want to do any data transformation, then it will ask you if you want to run this now or schedule it for later (run it now), and then it will import the database.

To get Enterprise Manager simply order the Free SQL Server 2000 demo from Microsoft and install the client tools only.

You can also use Visual Interdev to connect to your new SQL database, just add a new data connection to your project that points to your new database. You'll be able to create tables there also but it is limited compared to Enterprise Manager.

Data-types:

Time/date - I would use smalldatetime
Number - int
Text field up to 5000 characters - varchar
Primary key auto - int with Identity Yes 1,1
Text greater than 5000 characters - text

To make a column that requires unique fields simply add the UNIQUE constraint at the end of the field name when creating it.

It is worth the effort, but don't under-estimate the amount of work, and possible problems, that could arise. If you really take the time to learn and understand what you're doing you'll come out of this with valuable skills.

Hi again,

Now, don't laugh... I have found out that the server have already gotten EM installed

I have requested to get the database created, so hopefully I will be able to see what happens soon...

Thanks so far.
~casoper