MS-Access VBA and LC

[AxWald]

Hi,

anybody tried this already?

I have to do some heavy database work, and instead of to do it in LC, I'd by far prefer to utilize the powers of Access. All I need to do can easily be done within modules inside Access, as functions. I'd just need to call these functions from LC, using VBScript. Any way to do, anyone knows?
(Visual Studio 2010 Express would be available, still untouched ...)

Thanks,

Axel

PS: It's mainly SQL code that has to be executed on external databases, SQLite, MS-SQL and MySQL (Yes, all three together. With .csv files linked as tables, to add cream to the cake).

I'd have to build quite some 'recordsets' in memory, and have to 'join' them in my queries, with one another, or with real tables. I know Access can do this with ease, not sure if LC would be really helpful there. Basically I use MySQL, MS-SQL and .csv data to fill an SQLite database with whom I feed a web server ...

[Klaus]

Hi Axel,

please check the "do" command in the dictionary, sounds like this is what you need!
It lets you DO "strings" as AppleScript (Mac) and VBScript (Windows), maybe more, no idea


Best

Klaus

[AxWald]

Hi,

please check the "do" command in the dictionary, sounds like this is what you need!
It lets you DO "strings" as AppleScript (Mac) and VBScript (Windows), maybe more, no idea :D

Thx, that what I'm after ;-)

But I don't want to do:

Code: Select all

do MyStatement as VBScript

rather I'd love to do:

Code: Select all

do "D:\Databases\MyDatabase.mdb\MyModule.MyFunction" as VBScript with Param1, Param2

You see the problem?

I'm ready to export these modules/ functions as .bas or .vbs. What I want to achieve is, to use the power of the ADO/ DOA/ Jet model to do my number crunching (creating temporary recordsets, fill them with data, update these data, do strange calculations on these data, then writing the result to a SQLite database).

I assume I'm not able to use LC arrays in SQL statements, right? And writing 'em to temporally tables in an SQLite, then working with it, then overwriting/ deleting - not what I'd really want to do ...

Another idea would be to set up an access .mdb, make it do the work on startup, and have it shut itself down when ready. So far no problem - but will it work with a system account, without user interaction?

Questions over questions ...

Have fun!

[Klaus]

Hi Axel,

sorry, this special Windows stuff is way over my head


Best

Klaus

[bangkok]

I'm ready to export these modules/ functions as .bas or .vbs. What I want to achieve is, to use the power of the ADO/ DOA/ Jet model to do my number crunching (creating temporary recordsets, fill them with data, update these data, do strange calculations on these data, then writing the result to a SQLite database).

I assume I'm not able to use LC arrays in SQL statements, right? And writing 'em to temporally tables in an SQLite, then working with it, then overwriting/ deleting - not what I'd really want to do ...

Another idea would be to set up an access .mdb, make it do the work on startup, and have it shut itself down when ready. So far no problem - but will it work with a system account, without user interaction?
!

As you pointed out : user interaction.

You have it in LC.

As for data crunching, data processing... LC is a beast.

You can work directly on arrays. And then, write update your SQL.

So perhaps you should try to do EVERYTHING within LiveCode.

And if really you are a SQL hardcore guy... you could create temporary tables with SQLlite and do your stuff on them. Or why not, using a MySQL server in order to have access to a wide range of complex functions.

You got the MySQL and SQLite native drivers. And for MS SQL, you can use ODBC.

At the end... you could have an app with just one button ("Go !") instead of a complex multi clicks, multi windows system.

[AxWald]

Hi,

You have it in LC.
[...]
You can work directly on arrays. And then, write update your SQL.
So perhaps you should try to do EVERYTHING within LiveCode.

Thx, Bangkok. Yes, I know, I can do string operations & calculations on arrays, and LC actually is very good at this (That's why I'm happily back at LC :). But changing data in an array dependent of corresponding values in another array? Maybe regarding values in a third array? With different "join" relations? With strictly keeping/ converting the desired datatypes, and a rollback if an error comes up?

I'm sure, LC can do this. I have learned, ages ago, when it was still named "HyperCard", that you can do anything in it. But sometimes it can become a bit cumbersome to do it ...
(I wrote a complete book-keeping/ accounting system in the days of old, mimicking a relational database system in .txt files, using CompileIt! and 68K Asm to speed it up ...)

Later I realized it might not always be worth the labor (and Steve J. ruthlessly scrapped my Umax-Clones future anyways), and switched to Access as soon as I had learned about it. Which, as HyperCard/ LC, is another unique & awesome product. IMHO the best tool that MS ever coded/ bought!
It's often described as a "useless newbie database", instead it's actually the best "Database Frontend Development System" I ever got my hands on - and I'm in the business for 20+ years now. Believe me, Openoffice/ LibreOffice Base, Filemaker etc. are just a joke compared. 4D might be able to compete, but I grew some hate having to read french comments & poorly translated documentation at some time - they lost me as customer ...

So, regarding the lacking replys in this thread I assume that nobody ever tried to connect LC to Access via the "Do .... as VBScript" way. Well, I'll try for myself, and should I find out something, I'll report. Being able to use the powers of Access from within LC would be pure awesomeness, for me.
(I never got really accustomed to the DataGrid implementation in LC - cannot help, it looks like a Q&D hack for me and should be implemented as a thought-out native thing, not as a shady group of hard-to-tell items ... )

Meanwhile, for my current work, I'll use SQLite as "cache database". Love it! Small, fast, reliable :) Should have a place in the Hall of Fame for "Just Awesome Software", besides LC and Access ;-)))
(Customer wants results now, and not in the future, when I finished my VBScript hacks ...)

Thx for all that responded, I'll post here should I find out anything about connecting LC and Access. And I'd kindly ask that anybody that has tried already posts a little information here. As long as it's possible to share it.

Have fun!

[D4vidrim]

Hi,

please check the "do" command in the dictionary, sounds like this is what you need!
It lets you DO "strings" as AppleScript (Mac) and VBScript (Windows), maybe more, no idea

Thx, that what I'm after

But I don't want to do:

Code: Select all

do MyStatement as VBScript

rather I'd love to do:

Code: Select all

do "D:\Databases\MyDatabase.mdb\MyModule.MyFunction" as VBScript with Param1, Param2

You see the problem?

Hi,
not sure I can hep you, but when I needed to call a vba script (in Excel), I've written this code on LC:

Code: Select all

put specialFolderPath("temporary") & "/Zona_" & (the seconds) & ".txt" into tPath
put "set objExcel = GetObject(," & quote & "Excel.Application" & quote & ")" & return into TheScript
put "objExcel.Application.Run " & quote & "D_Scostamenti.xls!ImportAMC" & quote & ", " & quote & tPath & quote after TheScript
   Do TheScript as "VBScript"

To pass data, I use clipboard and a temporary file… maybe it's not the best way, but it works!

[Simon]

Hi Axel,
Not sure if this helps but, shove the VB script into a hidden field and "do field "vbscript" as VBScript"? Change the Peram's on the fly.
It's not exactly what you asked for so I may have wasted your time.

Simon

[AxWald]

Hi,

Code: Select all

[...]
put "set objExcel = GetObject(," & quote & "Excel.Application" & quote & ")" & return into TheScript
put "objExcel.Application.Run " & quote & "D_Scostamenti.xls!ImportAMC" & quote & ", " & quote & tPath & quote after TheScript
Do TheScript as "VBScript"

Thank you! I guess this should do the job! At least it showed me a way where to search further ;-)
Reading through the insanely weak MS help I actually found some hints, hope this will get my stuff done. I'll report, should it work :)

Not sure if this helps but, shove the VB script into a hidden field and "do field "vbscript" as VBScript"? Change the Peram's on the fly.

Usually I have my SQL statements stored in a text file that is read into a custom property at startup.
At runtime I read them from there when needed, replace my param placeholders, and then execute them.
Should I need to change any SQL statements, I change the text file, and send a message to the stack causing it to reload it - very handy when I have to change the statement in a running system ;-)

Should work with the VB code too, I hope.

It's not exactly what you asked for so I may have wasted your time. :(

Not at all! :)

Have fun!