I am new to VB2008 and visual studio 2008. I am very familiar with VBA and its use in Access and Excel.

I can [u]create tables and fields at run time </u>easily in access, and add or delete fields.

I get the impression that it doesn't work this way in VS /VB. I see lots of information on connecting to existing databases but nothing straightforward about table/field creation/deletion from code. Does this mean that I would, for example, create the database tables in Access, or SQLServer, separately.... and then use the VS/VB to create the user interface?

Can someone direct me to the appropriate resources for answering these questions? I would like to see some code that creates a table, adds various fields to it, then deletes the table...

Any help would be appreciated
Thanks

That is because you are programming directly against your data source. If you wanted to create a new table in SQL Server from a Visual Basic Application, for example, you would literally need to construct the SQL Statement to do so and then issue the command to the database.

What is the problem that you are having where you need to create tables at run time?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Well... its not that I have a problem and need to create tables at run time so much I guess, its just something that I learned/did recently. I was creating massive address lists from different sources where field names/types could be different, and also I was creating 3 additional fields (Filter1, Filter2, and Filter 3). By using a match list I could match the incoming field names with the internal field names and generate a new table using the internal names. Then I could create 3 filter tables ( essentially a list of filter terms (like subdivision) which are used in comboboxes for filter construction) Then the subsequent queries /filtering/reporting modules all work with no code modification.

Following is an example of what I did [u]IN ACCESS</u>, however, its not that I need to do this right now, what I am wondering is how do I create the tables in code. I guess you could say I have lived in a world (Access), where the database and the rest of the program co-exist in the same package. Now when studying the VB all references seem to use VB only to select and manipulate the data, not to create the actual database. Maybe I am asking ridiculous questions... thats possible ... I need someone to set me straight.

I have several access applications that I want to recreate in VB9 with visual studio, and I am asking "OK, where do we build the tables? Is this resource in Visual Studio? Do I create mdb's and then connect to them? Do I build the tables one by one? Do I create new sql database and then add tables to it?

So what your saying is the data source is/should be a separate entity and that the purpose of vb is to create the UI?

Sorry for the lengthy response.

Private Sub CreateTable()
Dim db As DAO.Database
Dim t_DI As DAO.TableDef 't_Data_Internal
Dim rec As Recordset
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim FieldPosition As Integer

Dim tblExists As Boolean
Dim tbl As DAO.TableDef

FieldPosition = 1
Set db = CurrentDb
DeleteTable ("t_Data_Internal")

Set t_DI = db.CreateTableDef("t_Data_Internal")
Set rec = db.OpenRecordset("t_Data_InternalFieldNames")

rec.MoveFirst
While Not rec.EOF
    If Not IsNull(rec(2)) Then
        'This is reading a data type table that was created automatically
        Set fld = t_DI.CreateField(rec(1), rec(3), rec(4))
        fld.OrdinalPosition = FieldPosition
        t_DI.Fields.Append fld
        FieldPosition = FieldPosition + 1

    Else
            End If


    rec.MoveNext
Wend
rec.Close
db.TableDefs.Append t_DI
RefreshDatabaseWindow
End Sub

Private Sub CreateFilterTables()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim rec As DAO.Recordset
Dim fld As DAO.Field
Dim frm As Form
Dim FilterName, tblName, qryName, frmName As String

Dim x As Integer
Dim FiltSQL As String
Dim FieldType, FieldSize As Integer

Set db = CurrentDb()

'check for filter tables and remove them
For x = 1 To 3
tblName = "t_Filt" & x
DeleteTable (tblName)

'fetch the fieldname of the current proposed filter
        FiltSQL = "SELECT t_Data_InternalFieldNames.ImportedDFN, t_Data_Inte" _
                   & "rnalFieldNames.InternalDFT, t_Data_InternalFieldNames.Int" _
                   & "ernalDFS" & vbNewLine & "FROM t_Data_InternalFieldNames" _
                   & vbNewLine & "WHERE (((t_Data_InternalFieldNames.InternalDFN)=" _
                   & Chr$(34) & "FILTER" & x & Chr$(34) & "));"

        Set qdf = db.CreateQueryDef("q_GetFiltName")
        qdf.SQL = FiltSQL
        Set rec = db.OpenRecordset("q_GetFiltName")
        rec.MoveFirst
        FilterName = rec(0)
        FieldType = rec(1)
        FieldSize = rec(2)
        'rec(0).
        rec.Close
        db.QueryDefs.Delete "q_GetFiltName"

'now create the table
Set tbl = db.CreateTableDef(tblName)
Set fld = tbl.CreateField(FilterName, FieldType, FieldSize)

tbl.Fields.Append fld
'fld.defa
db.TableDefs.Append tbl

'Insert the list of values into the new table
FiltSQL = "INSERT INTO " & tblName & " (" & FilterName & ")" & vbNewLine _
           & "SELECT t_Data_Internal.FILTER" & x & vbNewLine _
           & "FROM t_Data_Internal" & vbNewLine _
           & "GROUP BY t_Data_Internal.FILTER" & x & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL FiltSQL
DoCmd.SetWarnings True

'now create the query for the table
    qryName = "q_Filt" & x
'first delete the old query
    DeleteQuery (qryName)
    FiltSQL = "SELECT t_Filt" & x & "." & FilterName & vbNewLine & "FROM t_Filt" & x & ";"
 'then create the new one
    Set qdf = db.CreateQueryDef(qryName)
    qdf.SQL = FiltSQL

frmName = "sf_Filt" & x
Next x
RefreshDatabaseWindow
End Sub

Part of what is going on is all my access apps are becoming multi-user and, although this can be done in access, it seems as though it would be better to migrate to visual studio and have the benefits of built in multi user features.

To help illustrate what VB should/can encompass you need to start thinking in a multi-tier approach, often referred to as n-tier. What this means is that your application is broken up into layers where you have a UI (Presentation Layer), a Business Logic Layer (BLL) that would enforce any business rules that might be in place (for example a string value where an int value should be), and a Data Access Layer (DAL) that makes a physical connection to your database and issues commands against it.

So, as an example, your UI might have a ComboBox that needs all of the customers in Sales Area A this would trigger a call into your BLL to retrieve the data the DAL. The DAL would return the necessary data to the BLL, the BLL would do any necessary transformations on the data (if any), and send it back to the UI for display.

A good reason for why you would want to follow this approach is say, for example, your company moves away from using access and implements Oracle, there is a significant amount of change that will be required to migrate your application from using access to using the new Oracle server. By breaking your application up into layers such as these all you have to worry about migrating is your DAL, the BLL and UI should remain unchanged as long as you continue to return the same types of data from your DAL after the conversion.

Now to answer your question about creating Access tables from VB code: as I mentioned in my first post, yes this is quite possible although it is not a practice I employ. Consider this as an example: Say that you build multiple complex queries in your VB code and everything is running fine then there is a change made to the underlying database that breaks all of these queries. You now need to go back through all of your source code, find the query and edit it them. You may be asking what is the difference if I have to edit the query in the source code or alter a query/stored procedure in the database. The simple answer is, there isn't much difference in so far as the change is concerned, however, I am a firm believer that applications should not need to know or worry about any SQL code (other then that required to call a sproc), this is something that should be handled by the data source.

Does this make more sense?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Thankyou for your detailed response. Could you tell me if my understanding and thinking is gaining in the right direction?

Lets use a scenario where the datasource will be access mdb files, as this is what I am most familiar with, and I do not work with SQL server or Oracle. In this scenario I will build a solution using visual studio. Does the following look like what you are telling me..

1. Build the database tables using mdb files (this really has nothing to do with vb or vs at this point)

2. Build a data access layer (DAL).
    This layer contains all procedures that connect to and issue commands against the mdb data files.
    This DAL exists in VS as a "Project" that is a component of a larger "Solution".
    This layer will likely continue to evolve and be modified until all the SQL type activity that is required has been determined.
    This layers' purpose in life is to process requests from the BLL layer against the data layer.

3. Build a business logic layer (BLL).
    This layers sole purpose in life is to interpret requests from the UI (the presentation layer) to insure the requests are fit to be passed on to the DAL.
    This is the layer that transforms, manipulates, calculates, etc information into the form that the DAL layer will need in order for DAL to either update, retrieve, or store information.

4. Build the UI (Presentation layer)
    This is the dashboard that is available to the user. It is composed primarily of forms and reports and help systems to show the user how to use the system

Items 2, 3, and 4 come together as a complete solution that is pitted against item 1 (the database)

Going one step further ... a (admittedly) over simplistic way of looking at it from a [u]traditional access standpoint </u>

1. The tables container is the database layer
2. The queries container is the DAL layer
3. Modules and code that constitute the BLL layer
4. Forms and Reports constitute the UI or presentation layer.

The difference between the former and the latter is the flexibility of creation with in the confines of access and access vba [u]versus </u>the power and flexibility of a free standing windows app using visual basic.

Is this thinking on-track?? What would you add or change to my thoughts?

Are the mdb file components something that is available as part of VS and thus packageable as part of a final solution? Or is it really a separate thing (ie I have access and I started there by creating the tables)

Thankyou so much for your assistance.

Tom

Yes this is on track thinking, although you do not need to create seperate projects for each layer. You might have a folder in your project called BLL and one called DAL where the BLL folder contains all of the BLL Class files and vice versa with the DAL.

One pitfall that I see, however, is with Data concurency. Yes it is possible to package your MDB file with the app but your database are then working in a distributed enviornment so the issue of Person A and B editing the same record exists. Admitedly I have not worked with access for a long time but if memory serves me access supports this type of secnario, although it is not very effecient.

I would highly suggest, if nothing else, setting up a SQL Server Express (its free) system on your network so you have one central database that your app can run from.

Othen than that yes, you are on the right track.

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Thanks again Doug,

I am reviewing "SQL Server 2005 Express Edition Overview " at http://msdn2.microsoft.com/en-us/lib...sseover_topic2 and under the section Visual Studio Integration it states that:
 "SQL Server Express is installed with all editions of Visual Studio. Visual Studio installs SQL Server Express using the instance name SQLEXPRESS".

But the article seems to be dated maybe,
how do I determine if it was installed with my VS 2008?
How do I run SQLExpress from VS?

Can you point me to a reference of how to get started (using VS) or tell me how to start? (before I download and install SQLExpress

Yes. If you were to add an MDF file to your application you could then use the Database Explorer window to manipulate this file (create tables, stored procedures, views, etc). This type of behavior lends itself well to a Web Application where there is only 1 database for Y users. If you try to integrate an MDF file into a Windows app and try to redistribute it, it is my understanding, your users will need to have SQL Server Express installed on their machine to work with the data.

As you can see, this does not solve the problem of having multiple databases since each user would have a copy of the MDF in their application. I assume that you have a server somewhere on your network (or at least a PC that everyone on the network can see) I would install SQL Server Express on that machine and use that as your Data Source in your application thus giving you a central data source that all of your applications connect to.

For development purposes you can use Visual Studio to build the database, test, etc but when you go to deploy the application you should take the MDF file and restore it to the central SQL Server. Be warned, however: you will need to change the connectionstring in your application before you deploy it other wise it will be pointing to 'local' as opposed to the centralized server. This should seem obvious but I don't want you to run into any unnecssary "gotchas"! ;]

Does this make sense?

-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Ah ha!,

I just got back to this activity... Heres what I did..

1. Choose 'New Project' from the File menu
2. Choose Visual Basic and then Windows Forms Application from the New Project Dialog box
3. From the Project Menu choose 'Add New Item'
4. Under Common Controls Select 'Data'
5. Choose 'Service Based Database' template (which is identified as [u]'An empty SQL Server database for for service based data access</u>', this creates a .mdf file. Then I can add tables and define fields)
   (a different choice there was 'Local Database' ... which is identified as "[u]An empty SQL Server Compact 3.5 database for local data</u>" --- which is an sdf file -- what is the difference here? ... is this the one I should use if I am not going to run the program in an SQL server environment??)

I assume the former requires SQL Server to be present in the final deployment .. and the latter assumes it will not be (single user??) is this correct...

Now after we settle this I need to find references where I can construct tables and fields from codeNow we are full circle to the table creation from code... and why it could be important... I have existing Access databases where I can read the table designs programmatically and use them as instructions for creation of the new tables. (I already do this in access) Thus I don't have to create the tables manually. And it would be good exercise to gain proficiency manipulating table objects with visual studio (as I still will want to create and delete and modify tables from the VB interface)

I assume that 'SQL Server Compact 3.5' is the new version of SQLExpress? is this correct?

Also.. if I were to just use mdb files, in a non SQL server environment, would the program work with the mdb file even if the user does not have Access on their computer (because VB is taking the job over for access) or would Access still be required.

Thanks for all your help. It is really helping make better considerations for which way to proceed..

Tom