newbie question about SQL

[tomas_s]

So, I am working on this database in Access 2013, it has forms, reports etc. (you can view my other newbie questions).

Anyway, the database is to be converted into SQL (?), and be somewhere on some server of the company where I work. But I just don't "get" how the whole thing works.

If I read about SQL and Access, I understand that you can make SQL databases though something called "sharepoint" or something called "SQL Azure".

I tried to export the database into web form, but it was just a HTML page.


any way, the database I am working on is to be on a SQL server. How does that work? I seem to be missing some info, as you can probably read from this post. Please help me understand a bit about what SQL is, and how access databases are converted to SQL.

PLEASE!

 

[CJ_London]

This is a quick overview so all points may not be covered!

Most database applications will have a Front End (FE) and a Back End (BE)

The BE contains all of your tables and the FE all of your forms, reports queries and modules

The BE might be Access, SQL Server, MySQL, Sharepoint or other db provider and are usually shown as linked tables in the FE - so when you open a table, you are actually opening it in the back end. The FE in this case is Access, but could be a web interface or some other application

Some BE's may also contain queries which are referred to as Views and Parameter Queries and in a well designed db these will be how you will see and interact with the BE data from the FE. Views can be treated in much the same way as a table for the purposes of the BE.

Views are the equivalent of a select query
Parameter Queries do more - for example filtering on some value passed as a parameter or inserting and updating data.

So you may be migrating your tables to (for example) SQL Server together with queries that manipulate the data and modifying your FE to use the new BE. Alternatively the FE can be completely rewritten to take full benefit of the additional facilities available in the new BE - including enhanced data security.

Hope that makes sense!

 

[jdraw]

It isn't clear to me where you fit in the whole process of getting a database designed, developed and operational in SQLServer.

In any event your questions suggest some training in the design and development of databases generally.

Further to CJ's comments:

Here are a number of free video tutorials related to database design concepts.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

Good luck.

 

[tomas_s]

got it (i think), tables are BE and forms are FE.

01 So if I want to migrate to SQL server. Is there a conversion option in Access 2013 that one can simply do? I haven't found one, except when I start a new database, I have a "web app" option. But that option requires me to have the internet adress (or something).

02 What do I have to do to convert my database to SQL server? Because judging from the responses, I get the feeling that you can't just convert access databases to SQL.

 

[CJ_London]

There is no conversion as such - but you can migrate the data to SQL Server - there is a facility under database tools.

Alternatively from SQL Server you can import the data

However the front end has to be converted manually - every query, form and report plus modules and macros need to be reviewed.

If, having moved your data to SQL Server you then link to the data and keep the names of the linked tables the same as your original ones the changes should be minimal.

However if, as a result of this change your IT dept effectively has control of the data you will almost certainly find that changes will be required due to changes in access rights, naming conventions, the query language is slightly different etc.

I have some standard front ends which I use for a variety of databases, they may look the same to the user but under the bonnet there are some significant differences

You might find this link useful

http://support.microsoft.com/kb/237980

 

[tomas_s]

Do you guys ever use Microsoft InfoPath? for these kinds of things?

 

[gemma-the-husky]

access has an upsizing wizard to convert a jet/ace database into a SQL database

as long as your database is split, using a SQL database is pretty well seamless

1. with a split database, a table in the backend shows with an arrow next to it.

2. a table in a SQL backend shows as a word symbol

3. the connection management is slightly different in each case. the connection string given by currentdb.tabledefs("sometable").connect is different for an access backend, and other types of backend.

4. once you have a connected backend, virtuaslly everything else is the same.

 

[Steve@trop]

Hello Gemma,

I tried upsizing my database (a copy) and most of the queries didn't come over. Should I have split it first? Would that have helped the utility convert the queries without errors?

Thanks,

Steve