Using Access to put data into SQL Server

  • Thank you all for the very informative information. To be quite honest I do not work anywhere, well I don't work with databases. I am an auto repair shop owner who just completed a bachelors degree in computer information systems centered in Database Management (4.0 GPA). The project I was/am working on is for personal use, practice, and to keep my skills sharp and expanding so that if by some act of God I am offered a job in Database I will be able to accept it. I have not had much time recently to work on the project but I will be getting back to it. I appreciate your comments and the knowledge you are all passing on to me.

    Respectfully,

    Will

  • The quickest and easiest way for interfacing Access with a SQL database consists in creating links (attached tables or linked tables

    in Access terminology) that connect the Access database (.mdb or .accdb file) to the SQL Server.

    In the examples hereafter name of the SQL database is "Sales" with the following structure:

    USE [Sales]

    GO

    /****** Object: Table [dbo].[Tbl_Orders] ******/

    CREATE TABLE [dbo].[Tbl_Orders](

    [PK_Tbl_Orders] [int] IDENTITY(1,1) NOT NULL,

    [FK_Tbl_Customers] [int] NULL,

    [Order_Number] [int] NULL,

    [Quotation_Number] [int] NULL,

    --

    -- Other columns...

    --

    CONSTRAINT [PK_Tbl_Orders] PRIMARY KEY CLUSTERED ([PK_Tbl_Orders] ASC)

    ) ON [PRIMARY]

    /****** Object: Table [dbo].[Tbl_OrderLines] ******/

    CREATE TABLE [dbo].[Tbl_OrderLines](

    [PK_Tbl_OrderLines] [int] IDENTITY(1,1) NOT NULL,

    [FK_Tbl_Orders] [int] NOT NULL,

    [OrderLine_Number] [int] NOT NULL,

    --

    -- Other columns...

    --

    CONSTRAINT [PK_Tbl_OrderLines] PRIMARY KEY CLUSTERED ([PK_Tbl_OrderLines] ASC)

    ) ON [PRIMARY]

    /****** Object: Table [dbo].[Tbl_OrderLineItems] ******/

    CREATE TABLE [dbo].[Tbl_OrderLineItems](

    [PK_Tbl_OrderLineItems] [int] IDENTITY(1,1) NOT NULL,

    [FK_Tbl_OrderLines] [int] NOT NULL,

    [FK_Tbl_Items] [int] NULL,

    [Item_Number] [int] NULL,

    [Quantity] [int] NULL,

    --

    -- Other columns...

    --

    CONSTRAINT [PK_Tbl_OrderLineItems] PRIMARY KEY CLUSTERED ([PK_Tbl_OrderLineItems] ASC)

    ) ON [PRIMARY]

    /****** Object: Table [dbo].[Tbl_Customers] Script Date: 09/29/2012 19:29:02 ******/

    CREATE TABLE [dbo].[Tbl_Customers](

    [PK_Tbl_Customers] [int] IDENTITY(1,1) NOT NULL,

    [Customer_Code] [nvarchar](10) NOT NULL,

    [Customer_Name] [nvarchar](50) NOT NULL,

    --

    -- Other columns...

    --

    CONSTRAINT [PK_Tbl_Customers] PRIMARY KEY CLUSTERED ([PK_Tbl_Customers] ASC)

    ) ON [PRIMARY]

    /****** Object: Table [dbo].[Tbl_Items] ******/

    CREATE TABLE [dbo].[Tbl_Items](

    [PK_Tbl_Items] [int] IDENTITY(1,1) NOT NULL,

    [Item_Id] [int] NOT NULL,

    [Item_Description] [nvarchar](50) NOT NULL,

    --

    -- Other columns...

    --

    CONSTRAINT [PK_Tbl_Items] PRIMARY KEY CLUSTERED ([PK_Tbl_Items] ASC)

    ) ON [PRIMARY]

    The diagram of the database (relationships among tables) can be transcribed as:

    SELECT dbo.Tbl_Orders.*

    , dbo.Tbl_OrderLineItems.*

    , dbo.Tbl_OrderLines.*

    FROM dbo.Tbl_Orders

    INNER JOIN dbo.Tbl_OrderLines ON dbo.Tbl_Orders.PK_Tbl_Orders = dbo.Tbl_OrderLines.FK_Tbl_Orders

    INNER JOIN dbo.Tbl_OrderLineItems ON dbo.Tbl_OrderLines.PK_Tbl_OrderLines = dbo.Tbl_OrderLineItems.FK_Tbl_OrderLines

    LEFT OUTER JOIN dbo.Tbl_Customers ON dbo.Tbl_Orders.FK_Tbl_Customers = dbo.Tbl_Customers.PK_Tbl_Customers

    LEFT OUTER JOIN dbo.Tbl_Items ON dbo.Tbl_OrderLineItems.FK_Tbl_Items = dbo.Tbl_Items.PK_Tbl_Items

    The name of the Access database is SalesFE. Afterwards, we shall create another Access database (SalesBE) that will be used as a

    proxy.

    1. The first step consists in creating the five attached table (DSN-less) into SalesFE.mdb.

    a) Create and open SalesFE.mdb

    b) Create a new Module

    c) Paste this code into it:

    Sub AttachTables()

    '

    ' c_Connect contains the connection string to the SQL Server database.

    ' c_Tables contains the name of the five SQL Server tables that will be attached to the Access database.

    '

    Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"

    Const c_Tables As String = "Tbl_Customers,Tbl_Items,Tbl_OrderLineItems,Tbl_OrderLines,Tbl_Orders"

    Dim dbs As DAO.Database

    Dim tdf As DAO.TableDef

    Dim varTables As Variant

    Dim i As Long

    varTables = Split(c_Tables, ",")

    Set dbs = CurrentDb

    For i = 0 To UBound(varTables)

    If DCount("*", "MSysObjects", "Name = '" & varTables(i) & "'") > 0 Then

    CurrentDb.Execute "DROP TABLE " & varTables(i), dbFailOnError

    End If

    Set tdf = dbs.CreateTableDef(varTables(i))

    tdf.Connect = c_Connect

    tdf.SourceTableName = varTables(i)

    dbs.TableDefs.Append tdf

    Next i

    Set tdf = Nothing

    Set dbs = Nothing

    CurrentDb.TableDefs.Refresh

    End Sub

    d) Save the Module as: Mod_LinkTables

    e) Place the cursosr between inside the Sub AttachTables() procedure and execute it (F5)

    f) The five attached tables are now created in the database SalesFE.mdb.

    g) Close the VBA Editor.

    2. Next we shall create the Main form (Parent form) and its subforms (Child form).

    a) In the Database window, open the Forms tab and select New.

    b) In the New Form dialog box, select Design View and choose the table Tbl_OrderLineItems in the combo box then clock OK.

    c) When the form is in Design View, open the Field List window then drag the fields you want to appear on the form. Do not drag any key field (PK... or FK...).

    d) Open the Toolbox and add a ComboBox control to the form.

    e) Open the Properties window, select the newly created combo and change its following properties from their defaul values:

    - Name: Combo_Items

    - Control Source: FK_Tbl_Items

    - Row Source Type: Table/Query

    - Row Source: SELECT PK_Tbl_Items, Item_Id, Item_Description FROM Tbl_Items;

    - Column Count: 3

    - Column width: 0;1;3 (if you work in centimeters, convert to equivalent in inches if necessary).

    - Bound Column: 1

    - Limit to List: Yes

    f) Add a TextBox control to the form and change its following properties from their defaul values:

    - Name: Item_Description

    g) Open the VBA Editor window (ALT+F11) and paste the following code into it:

    Private Sub Combo_Items_AfterUpdate()

    Me.Item_Description.Value = Me.Combo_Items.Column(2)

    End Sub

    Private Sub Form_Current()

    Me.Item_Description.Value = Me.Combo_Items.Column(2)

    End Sub

    h) Save the form as: SF_OrderLineItems and close it.

    i) Repeat the steps a to c, selecting the Table Tbl_OrderLines instead of Tbl_OrderLineItems (no combo here).

    j) Save the new Form as SF_OrderLines.

    k) Repeat the steps a to d, selecting the Table Tbl_Orders instead of Tbl_OrderLineItems.

    l) Open the Properties window, select the newly created combo and change the followin properties from their defaul values:

    - Name: Combo_Customers

    - Control Source: FK_Tbl_Customers

    - Row Source Type: Table/Query

    - Row Source: SELECT PK_Tbl_Customers, Customer_Code, Customer_Name FROM Tbl_Customers;

    - Column Count: 3

    - Column width: 0;1;3 (if you work in centimeters, convert to equivalent in inches if necessary).

    - Bound Column: 1

    - Limit to List: Yes

    m) Add a TextBox control to the form and change its following properties from their defaul values:

    - Name: Customer_Name

    n) Open the VBA Editor window (ALT+F11) and paste the following code into it:

    Private Sub Combo_Customers_AfterUpdate()

    Me.Customer_Name.Value = Me.Combo_Customers.Column(2)

    End Sub

    Private Sub Form_Current()

    Me.Customer_Name.Value = Me.Combo_Customers.Column(2)

    End Sub

    o) Save the new Form as Frm_Orders.

    3. We'll now associates the forms together.

    a) Open the Child Form SF_OrderLines in Design View.

    b) Drag the icon representing the form SF_OrderLineItems from the Database Window to the Child Form SF_OrderLines open in Design View.

    c) Open the Properties Window which should display the properties of a SubForm/SubReport object named SF_OrderLineItems (if not select it on the form).

    d) Change the following properties from their defaul values for this object:

    - Name: Frm_Orders

    - Link Child Fields: FK_Tbl_OrderLines

    - Link Master Fields: PK_Tbl_OrderLines

    e) Save the form and close it.

    f) Open the Child Form SF_Orders in Design View.

    g) Drag the icon representing the form SF_OrderLines from the Database Window to the Child Form SF_Orders open in Design View.

    h) Open the Properties Window which should display the properties of a SubForm/SubReport object named SF_OrderLines (if not select it on the form).

    i) Change the following properties from their defaul values for this object:

    - Name: Child_OrderLines

    - Link Child Fields: FK_Tbl_Orders

    - Link Master Fields: PK_Tbl_Orders

    j) Save the form and close it.

    4. We can now create the back-end database.

    a) Create a new Access database, name it SalesBE.mdb (or .accdb) and open it.

    b) Import the Mod_LinkTables module from SalesBE.mdb, open it and execute the Sub AttachTables() procedure (F5), or recreate it from step 1.

    c) The five attached tables are now created in the database SalesBE.mdb.

    d) Close SalesBE.mdb.

    Note: SalesBE.mdb is not a "true" Front-End database. It contains no data, only the links to the SQL Server tables. The purpose is to remove all permanent attached tables from the Front-End (SalesE.mdb) and to recreate them dynamically from SalesBE.mdb, which will be the next step.

    5. If you closed SalesFE.mdb, reopen it.

    a) Delete the five attached tables (select each table in the Database windows and press the Delete key, then confirm).

    b) Open the form SF_OrderLineItems in Design view, open the Properties windows and delete the value of the RecordSource property.

    c) Save the form SF_OrderLineItems and close it.

    d) Open the form SF_OrderLines in Design view, open the Properties windows and delete the value of the RecordSource property.

    e) Save the form SF_OrderLines and close it.

    f) Open the form SF_Orders in Design view, open the Properties windows and delete the value of the RecordSource property.

    g) With the form SF_Orders in Design view, open the VBA Editor (ALT+F11) and paste the followin lines of code into it:

    Private Sub Form_Close()

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim varNames As Variant

    Dim i As Long

    With Me.Child_OrderLines.Controls("Child_OrderLineItems")

    .LinkChildFields = ""

    .LinkMasterFields = ""

    .SourceObject = ""

    End With

    With Me.Controls("Child_OrderLines")

    .LinkChildFields = ""

    .LinkMasterFields = ""

    .SourceObject = ""

    End With

    Me.RecordSource = ""

    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("SELECT [Name] FROM MSysObjects WHERE [Type] = 4 AND [Name] Not Like '~*';", dbOpenSnapshot)

    If rst.EOF = False Then

    varNames = rst.GetRows(999)

    For i = 0 To UBound(varNames, 2)

    If DCount("Name", "MSysObjects", "Name = '" & varNames(0, i) & "'") > 0 Then

    CurrentDb.Execute "DROP TABLE " & varNames(0, i)

    End If

    Next i

    End If

    rst.Close

    Set rst = Nothing

    End Sub

    Private Sub Form_Open(Cancel As Integer)

    Const c_Backend As String = "U:\Access\SalesBE.mdb"

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim varNames As Variant

    Dim i As Long

    Set dbs = CurrentDb

    Set rst = dbs.OpenRecordset("SELECT [Name] FROM MSysObjects IN '" & c_Backend & "' WHERE [Type] = 4;", dbOpenSnapshot)

    If rst.EOF = False Then

    varNames = rst.GetRows(999)

    For i = 0 To UBound(varNames, 2)

    If DCount("Name", "MSysObjects", "Name = '" & varNames(0, i) & "'") > 0 Then

    CurrentDb.Execute "DROP TABLE " & varNames(0, i)

    End If

    DoCmd.TransferDatabase acImport, "Microsoft Access", c_Backend, acTable, varNames(0, i), varNames(0, i)

    Next i

    End If

    rst.Close

    Set rst = Nothing

    Me.RecordSource = "Tbl_Orders"

    With Me.Controls("Child_OrderLines")

    .SourceObject = "SF_OrderLines"

    .Form.RecordSource = "Tbl_OrderLines"

    .LinkMasterFields = "PK_Tbl_Orders"

    .LinkChildFields = "FK_Tbl_Orders"

    End With

    With Me.Child_OrderLines.Form.Controls("Child_OrderLineItems")

    .SourceObject = "SF_OrderLineItems"

    .Form.RecordSource = "Tbl_OrderLineItems"

    .LinkMasterFields = "PK_Tbl_OrderLines"

    .LinkChildFields = "FK_Tbl_OrderLines"

    End With

    End Sub

    i) Save the form SF_Orders and close it.

    You know have a functional Access application that works with the tables stored on the SQL Server.

    6. Improvements.

    You'll probably want to rearrange the layout of the forms and perhaps add some Command buttons. If the users are allowed to modify the contents of the list Cuystomers and Items, you'll need to create forms for that purpose. You'll also need to set the Form Frm_Sales as the startup form of the database, or open it through an AutoExec macro. You can also build a Switchboard form.

    You can also add functionalities to then. As an example, here's how to add a Command button that will allow the user to search and display a specific Order Number:

    a) Open the form Frm_Orders in Design View.

    b) Add a Command Button control to the form.

    c) Open the Properties window and change the following properties of the Command button:

    - Name: Command_SearchOrderNumber

    - Caption: Search Order Number

    d) Select the On Click line and click on the button (...) at the right of the line.

    e) The VBA Editor opens and displays:

    Private Sub Command_SearchOrderNumber_Click()

    End Sub

    f) Add the following code between the Private Sub Command... and End Sub lines:

    Dim rst As DAO.Recordset

    Dim strOrderNumber As String

    strOrderNumber = InputBox("Enter the Order Number:", "Search Order Number")

    If Len(strOrderNumber) > 0 Then

    Set rst = Me.RecordsetClone

    rst.FindFirst "Order_Number = " & strOrderNumber

    If rst.NoMatch = True Then

    MsgBox "Cannot find the Order Number: " & strOrderNumber, vbInformation, "Not found"

    Else

    Me.Bookmark = rst.Bookmark

    End If

    rst.Close

    Set rst = Nothing

    End If

    g) Save the form Frm_Orders and close it.

    7. Securing the application.

    a) If you compile and convert SalesFE.mdb to SalesFE.mde, the objects it contains (only forms up to now) cannot be open in Design View any more and the VBA code behind them won't neither be accessible. When open, the Access application SalesFE.mde will only do what you allowed (i.e. Open the Frm_Orders form, load the attached tables from SalesBE.mdb and connect the Form and its subforms to their respective data sources).

    b) If you store SalesBE.mdb (or SalesBE.mde: you can also convert it) on a network share the access of which is reserved to the users allowed to open the application (individually or in an Active Directory Group), only those users will be able to successfully open SalesFE.mde: any unauthorized user that would try to open it woull cause an error to occur when the procedure Form_Open of the form Frm_Orders woull try to import the attached tables from SalesBE.mdb (Access denied).

    c) If you want to go further in the securization process, you can create Views on the server form each table (Tbl_Orders --> V_Orders, Tbl_OrderLines --> V_OrderLines, Tbl_OrderLineItems --> V_OrderLineItems, Tbl_Customers --> V_Customers and Tbl_Items --> V_Items), define the users (or a group of them) from the A.D. ouside the dbo Schema and grant them access to the Views. In such a case, the back-end will have attached tables to the Views. Notice that, since an attached table can have a different name than the name of its base table, you'll only have to change one line in the Sub procedure AttachTables(). The line:

    tdf.SourceTableName = varTables(i)

    would become:

    tdf.SourceTableName = Replace(varTables(i), "Tbl_", "V_")

    There are more efficient ways to interface an Access application with a SQL Server but, as far as I know, the one described here is one of the simplest and easiest to implement. The whole process can even be simpler if you use the various "assistants" (or "wizards") that Access provides, although I prefer not to use them.

    Note: This solution was developed and tested using Access 2003 (SP3) and SQL Server 2008 R2.

  • Maybe I'm alone on this but I cringe when I see table names prefixed with 'tbl'.

    IMHO, not a good practice.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (10/1/2012)


    Maybe I'm alone on this but I cringe when I see table names prefixed with 'tbl'.

    IMHO, not a good practice.

    Hi Welsh...out of curiosity ....why "not a good practice"?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • It is quite common in the Access world but I do not see any benefit in doing this.

    I hear a lot of laughs when discussions about tables in a SQL Server Database are prefixed with tbl.

    http://www.sqlservercentral.com/Forums/Topic953050-149-1.aspx#bm953125

    How many Microsoft Databases have you seen where the tables have a prefix?

    I understand that there are pros and cons but I do not plan on changing my Database Standards Document to mandate the use of prefixes for tables.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (10/1/2012)


    It is quite common in the Access world but I do not see any benefit in doing this.

    I hear a lot of laughs when professional come across tables in a SQL Server Database are prefixed with tbl.

    http://www.sqlservercentral.com/Forums/Topic953050-149-1.aspx#bm953125

    Perhaps I need to need to reconsider and update my Database Standards Naming conventions to mandate tbl as a Table prefix.

    fair enough...each to their own, and I do subscribe to not doing this in SQL databases as a general rule....however...

    in cases such as this, where we are using a FE app to link to SQL and particularly over a slow WAN link...then sometimes I find it prudent to initially copy some standing details table from SQL to Access (think citiesId.citiesname,citiestateId yadda)......by copying this across initially to the local app then the FE performance may well be enhanced.

    In such situations and to avoid confusion then I will name the local tables with a prefix....so this could be

    SQL name "CITIES"

    local name "tbl_CITIES" or "LOCAL_CITIES"

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • in response to rf44 mammoth post..... what an effort. :w00t: ..I do hope that the OP will appreciate the effort that you must have put into this.

    I haven't had time to walk thro your very detailed method so far...but I do have one initial question.....why is it required to create an Access FE and also an Access BE to link to SQL? Why cant the Access FE link directly to SQL using DSNless connections?

    maybe I have misread your post....so apologies if I have.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • a) It took a longer time to write and format the text of the answer than to actually create both Access databases (English is not my mother language). As I wrote in my post, this is a really fast way of creating an Access application connected to a SQL Server (less than an hour in this case).

    b) The only reason why the links to the tables on the SQL Server (attached tables) are stored on a "back-end" (although it's not a real Back-End, as it contains no data) is that no link to the SQL Server are stored in the Front-End, which must be distributed on every user's machine, while the Back-End can be stored and secured in a share on a server. If, for any reason, you must move the SQL database from one server to another, you don't need to change anything in the Front-End, just run the Sub AttachTables() procedure again in the Back-End after having changed the constant that contains the connection string (c_Connect). Moreover, as you don't use any ODBC link, a user's machine does not contain any reference to the SQL Server, except when the application is running. If this is not a concern, you can simply store the attached tables directly in the Front-End.

  • rf44 (10/1/2012)


    a) It took a longer time to write and format the text of the answer than to actually create both Access databases (English is not my mother language). As I wrote in my post, this is a really fast way of creating an Access application connected to a SQL Server (less than an hour in this case).

    b) The only reason why the links to the tables on the SQL Server (attached tables) are stored on a "back-end" (although it's not a real Back-End, as it contains no data) is that no link to the SQL Server are stored in the Front-End, which must be distributed on every user's machine, while the Back-End can be stored and secured in a share on a server. If, for any reason, you must move the SQL database from one server to another, you don't need to change anything in the Front-End, just run the Sub AttachTables() procedure again in the Back-End after having changed the constant that contains the connection string (c_Connect). Moreover, as you don't use any ODBC link, a user's machine does not contain any reference to the SQL Server, except when the application is running. If this is not a concern, you can simply store the attached tables directly in the Front-End.

    Typically you create an Access Database which Links to the SQL Server Tables.

    Then you create an Access Database that resides on the Client. THis Database links to the Access Database that contains the links to SQL Server and it contains the GUI as well.

    This is not required but it is common practice.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (10/1/2012)


    It is quite common in the Access world but I do not see any benefit in doing this.

    My guess is that quite often the people creating the objects are the same ones coding the VBA, and in that world, it is common practice to prefix a variable with the datatype, e.g. strStringVariable, lngLongVariable, etc. Just a guess though.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Greg Snidow (10/1/2012)


    Welsh Corgi (10/1/2012)


    It is quite common in the Access world but I do not see any benefit in doing this.

    My guess is that quite often the people creating the objects are the same ones coding the VBA, and in that world, it is common practice to prefix a variable with the datatype, e.g. strStringVariable, lngLongVariable, etc. Just a guess though.

    Yes, they use Hungarian Naming Convention in their code which is good.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Greg Snidow (10/1/2012)


    Welsh Corgi (10/1/2012)


    It is quite common in the Access world but I do not see any benefit in doing this.

    My guess is that quite often the people creating the objects are the same ones coding the VBA, and in that world, it is common practice to prefix a variable with the datatype, e.g. strStringVariable, lngLongVariable, etc. Just a guess though.

    You guessed right. This discussion began more than 20 years ago (not for SQL at that time).

    From Wikipedia:

    The original Hungarian notation, which would now be called Apps Hungarian, was invented by Charles Simonyi, a programmer who worked at Xerox PARC circa 1972–1981, and who later became Chief Architect at Microsoft.

    From MSDN:

    A note from Dr. GUI: Long, long ago in the early days of DOS, Microsoft's Chief Architect Dr. Charles Simonyi introduced an identifier naming convention that adds a prefix to the identifier name to indicate the functional type of the identifier.

    This system became widely used inside Microsoft. It came to be known as "Hungarian notation" because the prefixes make the variable names look a bit as though they're written in some non-English language and because Simonyi is originally from Hungary.

    As it turns out, the Hungarian naming convention is quite useful—it's one technique among many that helps programmers produce better code faster. Since most of the headers and documentation Microsoft has published over the last 15 years have used Hungarian notation names for identifiers, many programmers outside of Microsoft have adopted one variation or another of this scheme for naming their identifiers.

    Perhaps the most important publication that encouraged the use of Hungarian notation was the first book read by almost every Windows programmer: Charles Petzold's Programming Windows. It used a dialect of Hungarian notation throughout and briefly described the notation in its first chapter.

    If this naming convention was good enough for Dr. Simonyi, good enough for Charles Petzold, good enough for Microsoft, I don't see why it should not be good enough for anybody else. And if it's good enough for Windows programming and for Access (where it is indeed very common) I don't see any reason why it could not be used in SQL Server.

    As for the benefits you can get from it, I can see several ones, but others opposed to it could probably cite as many con's as I could enumerate pro's.

    From my point of view, the most important thing is that you should adopt a consistent naming convention and stick to it.

  • rf44

    WOW!!! Thank you for the very detailed post and the effort I know it took to creat it. It is very appreciated. It will take me some time todecifer all of this and understand everything that is happening but I will do it in due time. I am starting to believe that I need to take a few more VB classes or atleast start practicing with some tutorials. Again thanks for the response.

    Will

  • mrwillcostello (10/1/2012)


    ...atleast start practicing with some tutorials. Again thanks for the response.

    Will, it may not be kosher to point you to another site, but since you are looking for VBA help, I think it will be okay. I've found some great tips here over the years. http://allenbrowne.com/tips.html

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • mrwillcostello (10/1/2012)


    rf44

    WOW!!! Thank you for the very detailed post and the effort I know it took to creat it. It is very appreciated. It will take me some time todecifer all of this and understand everything that is happening but I will do it in due time. I am starting to believe that I need to take a few more VB classes or atleast start practicing with some tutorials. Again thanks for the response.

    Will

    You're welcome! 🙂

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply