none
Checking my access database for existing data RRS feed

  • Question

  •  

    Hi all,

     

    I have inherited a Visual Studio project from a previous work colleague, which is basically a program that enables our department to 'book in' all our jobs, and assign staff to them, etc. As well as handle all our leave,etc.

     

    The program uses visual studio, connected to a ms access database

    From what i can see the connections have been set up using the 'tableadapter confiquration wizard', to create all the SQL statements,

     

    What i am trying to sort out now, is that when a particular job is booked in, and staff assigned to it, i need to be able to check that the staff are not already booked out on another job, or on leave, and so on.

     

    As the data is being entered, how do i go about checking the database to see if there are clashes with existing data stored int he database?

     

    I am new to database programming, so please tell me if i have not made it clear to you,

     

    Thanks

    Tuesday, July 17, 2007 8:04 AM

All replies


  • I don't know the structure of your data but it sounds like what you're describing are simple table look-up commands that you can execute with a SELECT statement. The below example returns a count of the number of rows that satisfies the criteria in a table look-up.

    Dim AccessConn As System.Data.OleDb.OleDbConnection

    AccessConn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=c:\test files\db1 xp.mdb")

     

    AccessConn.Open()

     

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT Count(FieldName) AS RowCount FROM [TableName] WHERE FieldName = 'somevalue'", AccessConn)


    Dim RowCount As Int32

    RowCount = DirectCast(AccessCommand.ExecuteScalar, Int32)

     

    AccessConn.Close()

     

     

    Tuesday, July 17, 2007 1:09 PM
  • Thanks for your response, i hope i can try to make it a bit more clearer:

     

    In visual studio i have a dataset, which has been created with the wizard. In turn all the querys have been created via the wizard too.

    All of this code generated by the wizard is in my dataset.designer.vb file

     

    Is it in this file that i add all the SQL code to search the tables in the database, as new data is submitted?

     

    Apologise if i have not made it clear, am trying to get my head round what my colleague has done!

     

    Tuesday, July 17, 2007 1:59 PM