Run MS Access query and load the result table

[Dia791]

Hello guys,

I've an easy but challenging case...

Want to connect via ADO an Access database, run there an existing query (let's call it Q1) and load the results into Diadem.

The database file should be selectable by user (FileDlgShow or similar).

Meanwhile it's almost a week I try to get it, without solution 😞

There was an example (SQL Wizard) from Brad Turpin, but it seems to be very old and doesn't run...

We use Diadem 2015, btw.

Does anybody have a code "for dummies"?

Greetings,

Vassili

[winner]

A common problem when working with Access databases (and Office in general) is that the bitness does not fit. DIAdem 2015 is still 32 bit. Have you installed Office as 64 bit?
Maybe the download https://www.microsoft.com/en-us/download/details.aspx?id=54920 will help.
Please also read the additional information in the download.

This is an example that works for me:

Option Explicit
Dim dbName, conn, StrSQL, strConnect, rs
dbName = FileNameGet("Any", "FileRead", ,  "Access Files,*.mdb;*.accdb")
Set conn = CreateObject("ADODB.Connection")
' Connect to the database
' For Access 2000, 2002-2003 MDB, use the following connection string:
'"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'For Access 2007, 2010, 2013 ...:
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileDlgFileName
LogfileWrite strConnect
conn.Open strConnect
StrSQL = "Select * from Table1"
Set rs = conn.Execute(StrSQL)
Do While not rs.EOF
    LogfileWrite "Column1:" & rs.Fields("Field1") & " Column2:"& rs.Fields("Field2")
    rs.MoveNext
Loop

Winfried

[AndreasK]

Have a look at the following entry.

https://forums.ni.com/t5/DIAdem/How-to-communicate-with-SQL-database-from-drop-down-selection-on/m-p... 

Important is that you have an Access engine in 64bit. By either install the 64bit engine or the 64bit Access.

To figure out if access can be used use

AdoConStrGet

It is showing an Windows dialog wheer you can test if your Access is connectable.

Start with this dialog to get an usable connection string.

Please check the ADO example in DIAdem help it uses 

ArrayToChannels

at the end to load the results which helps a lot.

[Dia791]

Thanks for proposals!
I've identified an issue with common Diadem installation; the program creates errors without any reason...

Will need to let it check by out IT, or switch directly to Win-10 and Diadem-2019 if available (in my company).

Therefore it's difficult to accept anything as a solution now, sorry.

But the ball is on my side 🙂

Thank you again!