March 17, 2002 at 1:15 am
I am pretty much in confusion as to which d/b design to choose from
I have a Access datafile(.mdb) which is shared across in a LAN. My boss is planning to share it across a WAN.Ours is Head office...there are several branches at some different geographical areas...Hmmm...Is access a good choice in doing so.I mean to say I am totally new to Database handling and all....
I want to compare pros and cons of SQL and Access at this stage before deciding upon which d/b engine to use and also which database technologies to use....ADO's ???
Till now I have been using DAO's....
Can anyone please throw some light on this topic....I am totally confused right now as to which d/b should we use...
Thanks for reading my Post Rani
March 17, 2002 at 6:27 am
Once you get beyond a few users you generally use SQL. Also I think that across a WAN you'll see better performance. You can still build front ends in Access if you've already done some development work there. In Access DAO makes sense, if you're going to build a stand alone app than ADO (part of MDAC 2.6) is the best choice.
Andy
March 17, 2002 at 12:17 pm
In addition if the Wan is extremly slow hitting a SQL database is much better in the you can utilize stored procedures and get users only the data they need. Access will have to read and verify across the link. You could even setup merge replication and put local boxes around to deal with Wan speed if the data for each site does not have to fully interact with the other sites.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 17, 2002 at 9:35 pm
Thanks for your replies....
But as read I will have to have the DSN to establish a connection to the Database....as read there are 3 types of DSN-User,Sysytem and file....which one should I use....and why? also what ODBC driver should I specify if I am using Access and or Sql....Hmmm
Also security wise , I feel SQL is a better choice than Access....what do you say....
Please let me know about it...
Thanks again
Rani
March 18, 2002 at 4:56 am
SQL is far eaiser to deal with and better at admin and security as opposed to SQL, especially since tere is no mdw (workgroup file) that has to come into play. As far as SQL goes you do not have to setup a DSN or ODBC, the best way is to use ADO.
VB Snippet
'These are my public variables
Public SQLConn As New ADODB.Connection, SQLCmd As New ADODB.Command, SQLRSet As New ADODB.Recordset
'This is my connection string in a function
SQLConn.Open "Provider=SQLOLEDB.1;Initial Catalog=DatabaseNameHere;APP=ApplicationNameHere;Data Source=DNSNameorIPAddressHere", "UserName", "UserPassword"
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 18, 2002 at 5:20 am
System DSN's are stored in HKey_LocalMachine, User ones are stored in Hkey_CurrentUser, File DSN's are stored in the file system. You use a user DSN if you want the setting to be particular to the user, system if you want anyone logging into the machine to have access to it. File DSN's can be used however you want. Access does lean towards DSN's but you can still connect just a plain connect string if you wish. In ADO you can do the same thing as a DSN using a UDL file, or the connect string as in the example posted earlier.
Im not thrilled with the mdw file concept, but Access does support decent security and even replication. Overall I think SQL is better and the right choice. If money is an issue you may want to look at MSDE which is "smaller" version of SQL.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply