can someone describe to me the programmatic differences between a microsoft access mdb and access using sql?

Question

of course, i know that access has odbc connectivity to microsoft sql server. i have yet to do this though and am curious what changes would need to be made within forms so that the datasource points to a microsoft sql server table rather than the internal table within the access mdb file. is it that big of a difference?

Answer 1

It's easy to do - if your sql server is up and running and all the tables are there. First you need to create an ODBC connection. You do that outside of Access - using your control panel -> Administrative Tools -> ODBC. Follow the screens for creating a new connection. You will need to know your sql server's name, the database you want and a valid username/password for the db.



Now - back in Access you use Files -> Get External Data -> Link. Choose ODBC from the bottom of the screen where all the file types are listed. Find the ODBC connection that you just created. If all goes correctly you should see your tables listed. Choose any or all of the tables listed. Choose the SAVE PASSWORD option before you close.



Now Access can use these tables as if they were regular Access tables. One key point - your tables need to have primary keys or else you cannot edit them in Access.

Answer 2

That's a tall order - no room for it here. Basically, Access MDBs are fine for small databases, but once they get larger than 10 - 20 MB or so, they really crap out. If your building a database for a business and need it to be rock solid, use SQL Standard. Then you'll need to learn Transact SQL (Microsoft's version of SQL) but its not hard, if your either into it or used to programming.

Answer 3

Not sure if you can access directly to the sql tables from the form, but normally I would create links to the tables and them reference in the same way as a normal table.

Answer 4

because the two have been stepped forward via Microsoft, no longer surprisingly there is an exceedingly comparable seem and experience to the two of their GUIs. although, get right of entry to is a miles less-efficient DBMS and is aimed greater in direction of a single-consumer application. sq. Server has many greater bells and whistles and is ideal for networked use or maybe smaller corporation-point apps.

Answer 5

OK. I just converted over a VC++ application (actually *added* MSSQL support to existing support of Access) a couple of months ago.



The two are amazingly compatable. You mostly have to change your provider and connection information. In additon, the TIMESTAMP fields work a bit differently.



Here is a VC++ code snippet:



The generation tools in Visual Studio are pretty good.





if (catalogue && catalogue [0])

{/* Doing SQL */

dbinit.AddProperty(DBPROP_INIT_CATALOG, catalogue);

if (db_user && db_user [0])

{

dbinit.AddProperty(DBPROP_AUTH_USERID, db_user);

if (db_pass && db_pass [0])

dbinit.AddProperty(DBPROP_AUTH_PASSWORD, db_pass);

sprintf (logon_string, "UID=%s;PWD=%s;", db_user, db_pass ? db_pass : "");

}

else

sprintf (logon_string, "Trusted_Connection=Yes;");



sprintf (provider_string,

"Description=Using SQL;DRIVER=SQL Server;"

"SERVER=%s;APP=Using;WSID=%s;DATABASE=%s;%s",

dbname, dbname, catalogue, logon_string,

(db_extra && db_extra [0]) ? ";" : "", db_extra ? db_extra : "");

dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, provider_string);

SQL = 1;

string_delim = '\'';

}

else

{ /*Must be Access*/

dbinit.AddProperty(DBPROP_INIT_DATASOURCE, dbname);

if (!db_user || !db_user [0])

dbinit.AddProperty(DBPROP_AUTH_USERID, "Admin");

else

dbinit.AddProperty(DBPROP_AUTH_USERID, "db_user");

if (!db_pass || !db_pass [0])

dbinit.AddProperty(DBPROP_AUTH_PASSWORD, "");

else

dbinit.AddProperty(DBPROP_AUTH_PASSWORD, db_pass);

dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);

dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);

dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);

dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);

if (!db_extra || !db_extra [0])

dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, "");

else

dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, db_extra);

string_delim = '"';

}