Developing VB Forms Application to modify Access '.accdb' database

Question

Hi Guys

I have an issue that's driving me insane!!!

The task..

I need to develop an application the examines an existing MS Access database, extract data from specific tables/ re-arranges fields etc.

Both of the databases have many tables each with many fields...

The problem...

1. Visual Studio doesn't appear to have any help available online or offline!

2. Independent searches for help mostly display VBA solutions!

3. I've managed to access the source database, but occasionally can't because Access is left running as a service in the background by my application and I can't figure out how to prevent this?

4. The datatypes of the fields appear to be all wrong iv'e been trying DBType.Type but this sets wrong type in my application i.e  using DbType.String sets the Field type to Large Number in database file?

Information...

I'm developing a VB Forms application using Visual Studio 2017 to access/create a Microsoft Office 2016 Access database to be used by yet another application, the database has internal indexes, relations and queries that I'll need to create also.

Thanks in advance

Answer 1

Re #2: This works well on Bing:
.net interop "access database"
(you have to be willing to read C# and translate to VB)

Re #3: you have to close what you open. So if you CreateObject, you have to set it to Nothing when done. This is not an Access issue but general programming. It can get a bit tricky with Interop

Re #4: you need to use the Access and ACE object models, for example Access.Application, DAO.TableDef, DAO.Field, DAO.DataTypeEnum

---

-Tom. Microsoft Access MVP

Answer 2

Well, this is a "huge" topic.

It not clear if you are building this system to work with a given (an known) data structure, or you attempting to take say any table and have the system "generate" forms for you?

However, if you looking to build forms around a given and known database format? (just like typical access applications), then I would adopt datasets in visual studio (VS).

And if you looking to lay out the  land of these tables? Then again datasets are good choice.

So use the VS dataset designer system. The result is strongly typed data structures, and you can build data bound forms in  Visual studio that approaches the "ease" in which we build forms in Access.

So the resulting tables can then be just used in code, and all data objects become part of your code. You even get intel-sense for the field names in code when working with data.

You can get/grab a dataset with 2 lines of code – you don’t have to deal with connection strings, or even SQL if you do this.

Keep in mind that the approach used for entity frame work and that of datasets is VERY similar. I would simple choose one or the other. If you’re coming from Access, then datasets are a great choice. While datasets are more legacy in VS, they are mature, and tons of examples and books work using  datasets. Quite much any book “.net for dummies” will use this approach.

Access is great because you as an developer don't have to  spend large amounts of time wiring up forms to Access (ACE database) or SQL server. And we don't want to write all kinds of sql statements to insert, read, or update tables. So in VS, datasets handles all of this work for you.

So, the dataset designer in .net looks like this:

In above I just dropped in 3 tables from SQL server (but it could just as well been Access). So use the dataset designer to “pull in” all of the tables and structures you are attempting to work with. It not clear as to why you want to “write” some code to figure out the  data and tables? (why???) It just not clear to me where you want to go with your idea? There are great tools in VS for working with data. It will layout and show all the fields and tables for you.

With the above datasets? Well, I have a great view of all the tables now. And now in VS create a form, and drag in the dataset. It drops in a gridview for you by default, but you can just delete it. Now you can drag + drop in your fields from the dataset. You get this:

The above took LESS time then for me to write the above paragraph. Note the record navigation bar, the save buttons etc. I so far have NOT written one line of code for the above. All I did was drag the fields to the form (very much like one would  in Access).

Answer 3

So, you can use a data bound approach in VS, and give access a “run for the money” in terms of how much effort it takes to build a data bound form. Data binding is really a HUGE part of why Access is so great (you don’t spend time wiring up data to tables).

So at this point, if you just want to map out all the tables  and data? Well, again, I would use the dataset designer in VS. It will display and lay out all of the data tables for you. Even if I was not going to use the data binding features, it still a great way to lay out all of your tables and data.

These days, it really don’t make sense to attempt to “roll your own” data system, or even write code to “lay out” the given data and tables you have (regardless of using Access, SQL server or whatever).

All I am pointing out that even in VS these days, one would not write mountains of code to deal with data, nor write code to figure out the data structures. There are automated tools for this task.

This being an Access group of course means you barking up the wrong tree and wrong group. So spending time in  .net groups and circles is going to yield you FAR more useful information.

Answer 4

You will get better answers if you post an actual question with fewer complaints.

For one thing, it is not clear if you need to dynamically get a list of items, such as tables. That is a major influence on the solution and you say nothing about that.

---

Sam Hobbs
SimpleSamples.Info

Answer 5

>>. I've managed to access the source database, but occasionally can't because Access is left running as a service in the background by my application and I can't figure out how to prevent this?

Well, not really sure WHY you are automating a copy of Access? As a general rule, to get at the tables and data, you don’t need nor want to launch a WHOLE copy of Access, but just use VS to read and work with the tables.

Even in VBA/Access, I can “open” another different database and grab data. This does NOT require me to launch another copy of Access.

So, you can do this in VBA/Access:

   Dim db            As DAO.Database
   Dim strDataFile   As String
   Dim rst           As DAO.Recordset

   strDataFile = "c:\test3\test44.accdb"

   Set db = OpenDatabase(strDataFile)
   Set rst = db.OpenRecordset("select * from contacts")

Note how I did not launch a whole copy of Access.

Now you COULD create a whole new working copy of Access like this:

Sub test33()

   Dim MyAccess      As New Access.Application
   Dim strDataFile   As String
   Dim rst           As DAO.Recordset  
   strDataFile = "c:\test3\test44.accdb"
   MyAccess.OpenCurrentDatabase (strDataFile)

   Set rst = MyAccess.CurrentDb.OpenRecordset("select * from contacts")

End Sub

But golly gee – that second example launches a whole new copy of access (and all of the startup code launches, startup forms etc.). And if some stray dialog form pops  up, we are hooped!!!

So, even in Access to “open” another database we as a general rule don’t automate a whole copy of the Access application.

However, your question still stands:

How to correct shut down the above, if we wrote the above in vb.net.

The vb.net code would much like this:

   Dim MyAccess      As Object

   Dim strDataFile   As String

   strDataFile = "c:\test3\test44.accdb"

   MyAccess = CreateObject("Access.Application")

   MyAccess.OpenCurrentDatabase (strDataFile)

‘ do what ever we want with Access.

Now to correct shut down the “stray” copy of Access?

 MyAccess.Quit

 MyAccess = nothing

The above is “air code”, but it looks close to me. As I stated, it not at all clear why you launching whole automated copy of Access here to get at access data – it as a general rule should not be requied.

As noted, I would use the dataset designer – as that will build you a nice table view is a “whole” sytem  in .net that allows you to work with data tables, including that of working with related data tables.

>> The datatypes of the fields appear to be all wrong iv'e been trying DBType.Type but this sets wrong type in my application i.e  using DbType.String sets the Field type to Large Number in database file?

You have a choice of two providers here. Either you using the oleDB provider, or the odbc provider. (not clear which one your using). You “might” actually want to use the ODBC provider, since then all of your code work and efforts would work with say MySQL, SQL server, or Access with LESS changes.

I suppose oleDB providers are available for most, but this decision would certainly be worth a pot of coffee to think about long and hard.

If you adopt datasets, then you likely don’t have to worry about the above. But, lets assume we going to hand code this stuff the old way and time consuming way.

You code would be:

        Dim MyCon As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test2\test44.accdb")

        MyCon.Open()

        Dim strSQL As String = "insert into tblHotels2 (City) VAlues(@City)"

        Dim cmd As New OleDb.OleDbCommand(strSQL, MyCon)

        cmd.Parameters.AddWithValue("@City", "Edmonton")

However, the parameters above is not strong  typed.

If you want to strong type, then you don’t use dbType, but oleDB type.

You could replace  that parameters add with this:

        cmd.Parameters.Add("@City", OleDbType.VarWChar).Value = "Edmonton"

So, in above, we strong typed the parameter to a varWChar, which is a variable length Unicode string (and if you using accDB formats in access, then that’s the correct choice). VarChar should also work, but to be correct, access data is now Unicode.

So use oleDBType for the correct data types  here.

But having things like connecting strings in code etc. should be a thing of the past. The tools in VS can usually handle all these details, and the result is the above form in .net, and so far I not written one line of code.

With a dataset, then the  above to insert a row of data?

You get this:

        Dim Mdata As New test44DataSetTableAdapters.tblHotels2TableAdapter

        Dim MyHotels As New test44DataSet

        Dim OneRow As test44DataSet.tblHotels2Row = MyHotels.tblHotels2.NewRow

        OneRow.HotelName = "Egg Creek Hotel"

        OneRow.City = "Edmonton"

        MyHotels.tblHotels2.AddtblHotels2Row(OneRow)

        Mdata.Update(MyHotels)

So, in above, we get a strong typed data system, but more important is we get full intel-sense for all of the column names. And you quite much dealing with table objects.

In many ways, using strong typed datasets in vb.net is actually less work and effort then VBA to add or insert rows of data.

Regards,

Albert D. Kallal (Access MVP 2003-2017)

Edmonton, Alberta Canada