Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database in SQL (2003 or 2002)

    Right now I have five (5) databases with about 15 tables total. In SQL would I need a "Table/Database" build to contain the fields/data? Or is SQL some kind of Virtual database?
    We said it would take us about 5 days to create our 97 MSAcess database into MS Access 2003. Out SQL programmer said he could do it in a few hours. HOW?

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    I think you're confusing what SQL is and what a database is (Access or otherwise). SQL is a language that reads the data in a database and returns a sub-set of data that matches whatever criteria you choose. Therefore, SQL does nothing on its own, it needs a database to fetch the 'answer'. Now that you have built the database, stored the data in whatever tables and established the data relationships, you and then use SQL to do something with the data - extract patterns, count items that match something, etc.

    If the database design (table setup, data entry, field relationships, etc.) was done well then the 'reporting' via SQL is very quick.

    Does that make sense?

    Deb

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Yes I am very confused. Our SQL programmer told management that all I had to do was build a MS Access database with Links to SQL where the data is kept. So if SQL is a language where is the data?

    What would my Access Database links have? Do I still need to builda database with tables/fields?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database in SQL (2003 or 2002)

    >>build a MS Access database with Links to SQL where the data is kept<<
    When he/she says this I presume it to be SQL Server as the backend database which holds the data and Access as the front end database holding the links to the SQL Server database.

    All you need to do to convert your Access 97 databases is to open each one in Access 2003 (or 2002 if you have that) and answer Yes to the convert question. All you need to do is to give the converted database a new name. At the end of the conversion which will only take a few seconds if your Access 97 database has only a few objects (form, queries, etc), then just exit from the 97 database.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    I completed the second part of converting from 97 to 2003 Access. I did this so I could test out the forms. I still have trouble with the first part.

    We do have a SQL Server - Is this where a Database exists? What is the Backend database you mentioned? Can a database be created so fast?
    What would happend if I removed/deleted my Access Database?

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    SQL will work with any database (which contains the data ordered in records that are stored in tables). SQL Server is the name of Microsoft's big database (as opposed to Access which is Microsoft's 'little' database product). Don't confuse the name SQL Server with the language SQL. Within Access itself, you can 'query' the data using SQL just by typing the commands into a window and viewing the output. I'm not an Access whiz having only used Oracle but I know it can be done that way. Do a search in the Access help for SQL.

    Example: You have a table named Animal with fields (column names) called ID, Type, Size, Color. There are 5 records (rows) in the table that contain data (without data there can't be any records or rows just an empty table).

    Table=ANIMAL
    ID, TYPE, SIZE, COLOR
    123, dog, small, black
    197, dog, large, brown
    431, cat, small, orange
    36, dog,small, grey

    So if you wanted to get a list of all the records in table Animal where the size=small you could use an SQL statement such as (not exact syntax):

    SELECT * WHERE size=small FROM Animal

    This returns all records (hence the *) where the field called 'size' = small from the table called Animal. You'd get one record per row or in this case three records since there are three records where size=small.

    Deb

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    "What is the Backend database you mentioned?"

    Access IS the backend database. You just said you converted your Access 97 files to Access 2003 so that's where the data is stored. Now you need to run SQL against the data to extract the 'reports'

    Deb

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Ok, I think I have it here.... The SQL server is a database. Where a MS Access database can be on a network server or on my C: drive.

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Ok , about Access now. Our programmer said "You have to create a MS Access database to show the Links so it will write to the SQL database". Does this mean the data is kept on the SQL Server or on my MS Access database on a different Network drive. If the data is on my Network drive, why do I need SQL?

  10. #10
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    The SQL syntax for CREATE TABLE is

    CREATE TABLE "table_name"
    ("column 1" "data_type_for_column_1",
    "column 2" "data_type_for_column_2",
    ... )

    So, if we are to create the customer table specified as above, we may type in

    CREATE TABLE customer
    (First_Name char(50),
    Last_Name char(50),
    Address char(50),
    City char(50),
    Country char(25),
    Birth_Date date)

    Is this the only way to work with a SQL database? In Access I would go to the database and go into design mode to edit the fields, tables, querys.

  11. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Access can physically live on your local computer or a server. SQL Server (and Oracle and Sybase and Informix, etc. - other database products) have server components and local 'client' components so that you can sit at your local computer and view/edit the tabels that actually reside on the server.

    Access is just a much lower power, typically single user access product than the higher power 'server-based' databases. They do the same thing though - organize data in tables with rows and columns and then then the user needs to extract the data using some programming typically SQL.

    Deb

  12. #12
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    The SQL syntax for CREATE TABLE is

    CREATE TABLE "table_name"
    ("column 1" "data_type_for_column_1",
    "column 2" "data_type_for_column_2",
    ... )

    So, if we are to create the customer table specified as above, we may type in

    CREATE TABLE customer
    (First_Name char(50),
    Last_Name char(50),
    Address char(50),
    City char(50),
    Country char(25),
    Birth_Date date)

    Is this the only way to work with a SQL database? In Access I would go to the database and go into design mode to edit the fields, tables, querys.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database in SQL (2003 or 2002)

    <<If the data is on my Network drive, why do I need SQL? >>
    What is your definition of SQL in this sentence?

    Both Access and SQL Server can hold data. Normally when table grow too large for an Access database you would transfer all the data to a SQL Server database. In that instance you would thn use Access (or some other means) as a front end (where all the queries, forms, reports, etc are kept), this front end also contains the links to the SQL Server tables where the data is kept.

    In a Access database you can have the situation where you have a frontend Access database and a backend database. The fronend has all the queries, forms, etc and also the links to the tables in the backend database (where the data is kept)

    Hope this helps.

  14. #14
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Now I'm a bit confused.... you said you already converted the database from 97 to 2003 so you should just be able to 'view' the data in the database right now - without SQL. Just open Access and view the tables for the particular database. Are you saying that there is no data at all or that you need to know what SQL does and how to use it?

    To add data to the database you do it in several ways (assuming the tables exist):
    1) manually enter the data directly in Access; field after field, row after row (each row is one record, one data set)
    2) use SQL to enter the data for you
    3) use another program like VB or C or even Excel to connect to the Access database and load the data (it's essentially doing SQL too)

    Deb

  15. #15
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database in SQL (2003 or 2002)

    Quote: "Our programmer said "You have to create a MS Access database to show the Links so it will write to the SQL database".

    Since the programmer is specifically using the term "SQL database" then it sounds like you have the database product called SQL Server on some remote server somewhere. If yes then the 'links' s/he is talking about probably refer to how you'd use Access to "point to" a set of tables/data in that remote SQL Server database. In this case Access is like a front-end to view the actual data stored remotely. I don't see the point of doing this at all, but then I don't know your circumstances.

    Deb

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •