| Author |
 Topic Search  Topic Options
|
dingdangdo 
Newbie
Joined: 13 October 2005
Status: Offline
Points: 7
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Internet Search Engine - Access to MSSQL
Posted: 13 October 2005 at 7:01am |
Hi all, and in particular - the seniors
I have read quite a bit that Access is not suitable for concurrent users.
If I converted MS Access to MS SQL, what changes would be required to make the ASP work with MS SQL?
For those in the know, what changes to the ASP (if any) would be requried specifically. The reference to the DB....?
Secondly, what is the best, or most efficient way to make this transfer from mdb to MS SQL?
mdb convert to MS SQL, or MySQL?
Thanks
|
 |
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 13 October 2005 at 1:16pm |
Without getting into an argument whether to use mySQL or MSSQL, go with what your host offers. If they have MSSQL for the same or similar price go with it.
As for the upgrade you can use the Access Upsize Wizard which I do not necessarily recommend, I had nothing but problems with it. I would, if you have Enterprise Manager, import the RAW data with DTS, make sure the indexes etc are set correctly and simply change the connectionstring in your code to something like
from
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
to
"Driver={SQL Server};Server=servername;Database=yourdb;Uid=youruser;Pwd=asdasd;"
|
|
|
|
dingdangdo
Newbie
Joined: 13 October 2005
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 13 October 2005 at 2:20pm |
Hi Michael.
Thanks for the reply.
Sorry for the dim assumptions, its just that I have been a web designer for 3 years (only!), and Server Side / DB is something I really want to get into.
I will take a look at Access Upsize Wizard. What problems did you have?
My Host seems to provide Microsoft SQL Server 2000 : I assume that this is MS SQL?
Since I am using web samples as a way to learn the basics, I am under no pressure to get the solution asap.
Therefore, I am considering designing a db from scratch. Especially since my host seems to provide SQL server. So I will have an empty db to start with.
Thanks for highlighting the change required in the connection string.
I think if i can use one ASP application, and just try different dbs, then this will provide me with a lot of opporunity to see what changes are needed in the code itself. Something that I am sure to come back to this forum with.
If my host provides SQL server, is there much point in me attempting to install MySQL on my own machine? Probably not.
Yeh, what problems did you have with access upsize wizard?
|
|
dingdangdo
Newbie
Joined: 13 October 2005
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 13 October 2005 at 2:50pm |
I have just checked
My hosting provides MySQL, which I have just had the plessure of setting up my first Table Row with PHPMyAdim
What is a MySQL equiv of AutoNumber - is it auto_increment? Because auto_incr does seem to be available, whilst DATE, NUMBER, etc is?
So, MySQL or MS SQL? From what I have read, the latter is better
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2005 at 12:08am |
It really depends what you are doing, the latter is better for large scale apps for sure but if your host provides msSQL and you are just beginning it's a good entry point.
The main problem I have with the upsize Wizard is that Access seems to think it is smarter than me and tries to dictate how to do things, I like the control of using a better ETL tool like DTS>
|
|
|
|
dingdangdo
Newbie
Joined: 13 October 2005
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2005 at 4:35am |
Hi Michael
Sure....Well, what I am going to do first, is to make a MySQL and MSQL version of my Access Database....
That is, make both SQL databases with the same table name, field names, and datatypes. The issue is, Access datatypes are different from MySQL, and MSSQL.
But If can make a blank SQL db, with perhaps only a couple of entries, then I can chnage my ASP samples accordingly.
Two things I will need to find, an Equiv table of datatypes, and alternative methods of getting Auto-number, for example.
I get the jist of what you are saying about Upsize.
Another reason I am going to follow MSSQL more so, is because I have read that it is better for the ASP (Minimum changes required) if I switch to MS, rather My?
Enterprise Manager is something that I cannot have, it costs a few hundred right?
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2005 at 9:17am |
I am sure there is an equiv table on data types somewhere around.
Autonumber in Access is INT in SQL Server with the Increment(1,1) Property set.... If you compare Northwind in Access and SQL Server you should be able to compare most you need.
As for Enterprise Manager, well you can download the SQL Server 2000 Trial from Microsoft only install Enterprise Manager, of course you will be waiting for it to expire which EM never will.... Besides that there are plenty of other toold out there like Enterprise Manager WEb Admin which is basically a web based EM from MS, is not bad at all for most things.
|
|
|
|
dingdangdo
Newbie
Joined: 13 October 2005
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 14 October 2005 at 9:40am |
Well
I have discovered that an equiv to Access AutoNumber in MySQL would be something like: -
CREATE TABLE comments (
COMMENT_ID int(11) NOT NULL auto_increment,
NAME varchar(50) default NULL,
PHONE varchar(50) default NULL,
HOBBIES varchar(20) default NULL,
EMAIL varchar(50) default NULL,
SUBMIT_DATE datetime default NULL,
COMMENTS longtext,
ANSWERED tinyint(4) default NULL,
PRIMARY KEY (COMMENT_ID),
KEY PRIMARY_KEY (COMMENT_ID)
)
No limitations on use with the EM as Trial (partial convergence, etc etc)?
|
|
