Results 1 to 5 of 5

Thread: [.NET] Automating Microsoft Access

  1. #1
    Saiyan Race
    j03's Avatar
    Joined
    Dec 2011
    Posts
    13,720
    Userbars
    166
    Thanks
    5,906
    Thanked
    33,076/6,608
    DL/UL
    23/36
    Mentioned
    3,867 times
    Time Online
    563d 4h 55m
    Avg. Time Online
    3h 13m

    [.NET] Automating Microsoft Access

    To convert to C#.NET, try using an online converter like:

    (you need an account to see links)

    I made these functions for my work to help automate Microsoft Access to the point where everything is on auto-pilot and the end-user doesn't have to even open Access. Your basic functions for manipulating a database. Posting these for anyone who might find them useful.

    Note: Basically, create an Access file and save it as an MDB type, which is 2002-2003 I believe.

    At the top of your project, either in your class or form add the reference to:

    Code:
    Imports System.Data.OleDb
    In your variable declarations, add:

    Code:
    '--- MS Access Connection Variables ---
        Public aString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Z:\AppData\Databases\test.mdb" '<- LOCATION OF MDB FILE
        Public aConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection
        Dim aCommand As New OleDbCommand
        Dim aDR As OleDbDataReader
        '-------------------------
    I made aConnection public so you can set the connection string not just where you declare it, in case you need to change between database connections, working with multiple instances, etc.

    This function allows you to insert to your database table created in Access. As you can see, I am formatting my string as it makes sending the query a little more managed and easier to read.

    Code:
    Public Sub InsertDB(ByVal dbTable As String, ByVal BodyID As Integer, ByVal ItemName As String, ByVal XP As String, ByVal XM As String, ByVal YP As String, ByVal YM As String, ByVal ZP As String, ByVal ZM As String)
            'Write to DB
            aCommand.CommandType = CommandType.Text
            aCommand.CommandText = String.Format("INSERT INTO {0} VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')", dbTable, BodyID, ItemName, XP, XM, YP, YM, ZP, ZM)
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aCommand.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            aConnection.Close()
        End Sub
    This function returns a string of what you are looking for, based on your input. As you can see, this can probably be managed a little more efficiently but it works for what I need. If someone has a better way of doing this, feel free to share.

    Code:
    ''' <summary>
        ''' Grabs an item from the specified column based on our DB key identifier
        ''' </summary>
        ''' <param name="dbTable">Specified DB table to scan</param>
        ''' <param name="keyColumn">Key column identifier</param>
        ''' <param name="keyItem">Key identifier</param>
        ''' <param name="FindColumn">Column to grab data from</param>
        Public Function FindDB(ByVal dbTable As String, ByVal keyColumn As String, ByVal keyItem As String, ByVal FindColumn As String) As String
            'Read from DB
            FindDB = vbNullString
    
            aCommand.CommandType = CommandType.Text
            aCommand.CommandText = String.Format("SELECT * FROM {0} WHERE {1} = '{2}'", dbTable, keyColumn, keyItem)
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aDR = aCommand.ExecuteReader
                If aDR.HasRows <> True Then
                    MessageBox.Show("Found no results for '" & keyItem & "'.")
                    aConnection.Close()
                    Return vbNullString
                End If
    
                While aDR.Read()
                    FindDB = aDR(FindColumn).ToString()
                End While
    
                aConnection.Close()
                Return FindDB
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            aConnection.Close()
    
            Return vbNullString
        End Function
    Delete a row from our table.

    Code:
    Public Sub DeleteDB(ByVal dbTable As String, ByVal ItemType As String, ByVal Item As String)
            'Delete from DB
            aCommand.CommandType = CommandType.Text
            aCommand.CommandText = String.Format("DELETE FROM {0} WHERE {1} = '{2}'", dbTable, ItemType, Item)
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aCommand.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            aConnection.Close()
        End Sub
    Update a cell in our table.

    Code:
    Public Sub UpdateDB(ByVal dbTable As String, ByVal ItemType As String, ByVal Item As String, ByVal BodyID As String)
            'Update items in DB
            aCommand.CommandType = CommandType.Text
            aCommand.CommandText = String.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", dbTable, ItemType, Item, "BodyID", BodyID)
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aCommand.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            aConnection.Close()
        End Sub
    This one scans our table and returns the specified column into a list. Can be useful, here I have the column name hardcoded so you might want to make that dynamic.

    Code:
    ''' <summary>
        ''' Scans our DB table and pulls all item names into our listbox
        ''' </summary>
        ''' <param name="lstItems">Listbox to store body names</param>
        Public Sub ScanDB(ByRef lstItems As ListBox)
            'Read from DB
    
            aCommand.CommandType = CommandType.Text
                    aCommand.CommandText = String.Format("SELECT * FROM {0} WHERE {1} LIKE '%{2}%'", _
                                                 "Block", "ItemName", "")
    
            aCommand.Connection = aConnection
    
            aConnection.Open()
    
            Try
                aDR = aCommand.ExecuteReader
    
                While aDR.Read()
                    lstItems.Items.Add(aDR("ItemName").ToLower())
                End While
    
                aConnection.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            aConnection.Close()
        End Sub
    If anyone has suggestions on what they might need, feel free to ask. I posted the basic functions I thought users might find useful. And I'm definitely open for suggestions to change/add to this code.
    Last edited by j03; 03-07-2015 at 11:56 AM.
    (you need an account to see links)
    (you need an account to see links)(you need an account to see links)

    ------------------------
    [02/24/2013] Stealth CORE is made into the first standalone Neopets auto-player.
    ------------------------


  2. The Following 6 Users Say Thank You to j03 For This Useful Post:

    ailunbee (09-10-2018),Aura (03-07-2015),Coolguy (03-10-2015),siaospear (03-20-2016),Water (03-07-2015),Zachafer (03-07-2015)

  3. #2

    Joined
    Dec 2012
    Posts
    0
    Userbars
    0
    Thanks
    0
    Thanked
    3/3
    DL/UL
    16/0
    Mentioned
    Never
    Time Online
    2h 11m
    Avg. Time Online
    N/A
    You are my here joe

  4. #3
    Zachafer's Avatar
    Joined
    Dec 2011
    Posts
    1,235
    Userbars
    11
    Thanks
    769
    Thanked
    1,466/678
    DL/UL
    98/0
    Mentioned
    512 times
    Time Online
    24d 13h 9m
    Avg. Time Online
    8m
    @(you need an account to see links) putting variables directly into a query is a huge security risk, namely risk of injection. You should be using parameters to pass data to the queries. This prevents any kind of injection as well as preserving variable data types (not everything is a string).

    For example, your InsertDB block would look [something] like:
    Code:
    Public Sub InsertDB(ByVal dbTable As String, ByVal BodyID As Integer, ByVal ItemName As String, ByVal XP As String, ByVal XM As String, ByVal YP As String, ByVal YM As String, ByVal ZP As String, ByVal ZM As String)
            'Write to DB
            aCommand.CommandType = CommandType.Text
             aCommand.CommandText = "INSERT INTO @db VALUES (@1,@2,@3,@4,@5,@6,@7,@8)"
            aCommand.Parameters.AddRange(new OleDbParameter[]{
                new OleDbParameter("@db", dbTable),
                new OleDbParameter("@1", BodyID),
                new OleDbParameter("@2", ItemName),    
                new OleDbParameter("@3", XP),    
                new OleDbParameter("@4", XM),    
                new OleDbParameter("@5", YP),    
                new OleDbParameter("@6", YM),
                new OleDbParameter("@7", ZP),
                new OleDbParameter("@8", ZM)})
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aCommand.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            aConnection.Close()
        End Sub
    Instead of using .Close(), you should use the (you need an account to see links)

  5. The Following User Says Thank You to Zachafer For This Useful Post:

    j03 (03-20-2016)

  6. #4


    siaospear's Avatar
    Joined
    Jan 2016
    Posts
    185
    Userbars
    4
    Thanks
    441
    Thanked
    153/79
    DL/UL
    27/0
    Mentioned
    37 times
    Time Online
    14d 10h 13m
    Avg. Time Online
    6m
    Didn't understand any of it but I'm sure those who code would appreciate it. Cheers!


    Sent from my iPhone using Tapatalk

  7. #5
    Saiyan Race
    j03's Avatar
    Joined
    Dec 2011
    Posts
    13,720
    Userbars
    166
    Thanks
    5,906
    Thanked
    33,076/6,608
    DL/UL
    23/36
    Mentioned
    3,867 times
    Time Online
    563d 4h 55m
    Avg. Time Online
    3h 13m
    Quote Originally Posted by Zachafer View Post
    @(you need an account to see links) putting variables directly into a query is a huge security risk, namely risk of injection. You should be using parameters to pass data to the queries. This prevents any kind of injection as well as preserving variable data types (not everything is a string).

    For example, your InsertDB block would look [something] like:
    Code:
    Public Sub InsertDB(ByVal dbTable As String, ByVal BodyID As Integer, ByVal ItemName As String, ByVal XP As String, ByVal XM As String, ByVal YP As String, ByVal YM As String, ByVal ZP As String, ByVal ZM As String)
            'Write to DB
            aCommand.CommandType = CommandType.Text
             aCommand.CommandText = "INSERT INTO @db VALUES (@1,@2,@3,@4,@5,@6,@7,@8)"
            aCommand.Parameters.AddRange(new OleDbParameter[]{
                new OleDbParameter("@db", dbTable),
                new OleDbParameter("@1", BodyID),
                new OleDbParameter("@2", ItemName),    
                new OleDbParameter("@3", XP),    
                new OleDbParameter("@4", XM),    
                new OleDbParameter("@5", YP),    
                new OleDbParameter("@6", YM),
                new OleDbParameter("@7", ZP),
                new OleDbParameter("@8", ZM)})
            aCommand.Connection = aConnection
            aConnection.Open()
    
            Try
                aCommand.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    
            aConnection.Close()
        End Sub
    Instead of using .Close(), you should use the (you need an account to see links)
    I made this for my company which is an office and everything is run internally. But definitely optimize for better security if using for something more broad.

    I also adjusted my code and learned about the using statement a while back. Glad you pointed it out... Maybe I will post an update to this.
    (you need an account to see links)
    (you need an account to see links)(you need an account to see links)

    ------------------------
    [02/24/2013] Stealth CORE is made into the first standalone Neopets auto-player.
    ------------------------


Tags for this Thread

Posting Permissions

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