If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] Create array from tables in different MS Access databases

Hi Everyone!

This is my first post in a long time, although this is my go-to source for finding great information--you all are the best!! I am currently in the process of migrating (technically re-writing) an old VB 6 program in VB.NET (2017).

The app is a customized software that processes data supplied as MS Access tables, then sends it to PowerPoint, Word, and/or Excel to create reports/tables/charts/slides. I am delving into the world of ADO.NET and am struggling translating my old ADO (and limited DAO).

I have figured out successfully how to create data readers, data tables, data sets, and data relationships. I was able to successfully create data readers and tables, fill an array, and send this data to Excel. What I can't figure out, is how to do this when the data is in two separate Access databases. Below is the code I currently have.

There is a dataset that includes the data I need from both databases, I'm struggling with how to join those two tables then fill an array with the result of the join.

I did try a LINQ query but couldn't figure out how to get an array from that result. There are 23 columns of data I need. The column order is not left-right (as in table 1 is on the left then table 2 is on the right)... I have data analysts that use the resulting report in other processes they do so the order matters to them.I have comments in the code where I'm stumped... this is my latest version where I was attempting the use of a data relationship. Is this the right way to go, or LINQ? I really appreciate anyone that has some time to give me some advice!!

Re: Create array from tables in different MS Access databases

Hi IkkeEnGij,

Thanks for the reply, I very much appreciate your assistance.

Pardon my naivety, but that looks like what I used to write in the old ADO, I'm trying to translate it to the new ADO.NET. From what I've found so far, it's pretty easy to pull from one database, but everything I find requires the connection object to the database... how do you create that connection to both without doing them separately into a data set (which is what I'm currently doing)?

to hunt a species to extinction is not logical !since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

After reading through your suggestion Chris it dawned on me that I was making this particular problem harder than I needed to and was overthinking it. One of the databases I create in previous parts of the program so I am simply inserting the data into that one for the Join, then delete it when I'm done. I had just started creating that second database before they abruptly took my VB 6 away so I am not used to it as part of the big picture just yet. The other databases I have less control over, and this approach works for when I have to join from three databases, which will come up during the rewrite as well.

So using my connection objects and .ExecuteNonQuery I will have the tools to cover everything I have to rewrite. This will also allow me to move all my old SQL statements over. Easy-peasy (she says as she slaps her head in the "well duh" moment!!!).

Later I will learn more about LINQ, but for now will be able to finish my task at hand much faster.

After reading through your suggestion Chris it dawned on me that I was making this particular problem harder than I needed to and was overthinking it. One of the databases I create in previous parts of the program so I am simply inserting the data into that one for the Join, then delete it when I'm done. I had just started creating that second database before they abruptly took my VB 6 away so I am not used to it as part of the big picture just yet. The other databases I have less control over, and this approach works for when I have to join from three databases, which will come up during the rewrite as well.

So using my connection objects and .ExecuteNonQuery I will have the tools to cover everything I have to rewrite. This will also allow me to move all my old SQL statements over. Easy-peasy (she says as she slaps her head in the "well duh" moment!!!).

Later I will learn more about LINQ, but for now will be able to finish my task at hand much faster.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.