Vb To Db?

[Cory]

I have several macros for one client that rely on data in an Access Db. Since ME can’t directly access this they routinely export queries to text files. I know one can access a Db fairly easily with Visual Basic but I am not a programmer. I reckon if one knew VB and ME it would be feasible to create a link between worlds. Has anyone any experience with this? All comments welcome.

[terrypin]

Excel can read dBase (.DBF) files, so one approach might be to try the 'Excel Wizard' introduced at http://pgmacros.com/community/index.php?showtopic=632

 

--

Terry, West Sussex, UK

[Cory]

Unfortunately even if it were able to access it without opening Excel it wouldn't make any difference. The linked data in a spreadsheet can't be updated until the file is actually opened.

 

I'm curious, does Randall's routine require the file to be open or is it accessing it directly? If it does not maybe he could create an equivelant for Access.

 

Thanks for the suggestion.

[terrypin]

Sorry, can't help you there. I just recalled seeing that page very recently and thought it might give you a promising basis to pursue. Also added it to my 'list of stuff to explore sometime'

 

--

Terry, West Sussex, UK

[joe]

Cory -

 

I'll bet this is what you're looking for:

 

A majority of our clients are using Macro Express to control VBScript scripts that use ADO to access Excel workbooks as databases without the need for launching Excel. Extracting data, writing data, and so forth. But none of them have any use for Access. I imagine that the steps required are almost identical with those of Excel.

 

So yes, you can use database techniques on Excel and Access. For that matter on text and CSV files, too! Hang on to your shorts, here's an example:

 

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=X:\Data\Addresses.xls;Extended Properties=""Excel 8.0;HDR=Yes;"";" 
objRecordset.Open "Select * FROM [Main$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText

strSearchCriteria = "Name = 'Joe Weinpert'"
objRecordSet.Find strSearchCriteria
objRecordset("City") = "Cleveland"
objRecordset.Update
objRecordset.Close
objConnection.Close

The above code (let's call it "NameAddress.vbs") would:

1. Open an existing Excel sheet named "X:\Data\Addresses.xls" as a database
   
2. Open the sheet named "Main" as a table (in this example the whole table is a record set)
   
3. Search the column titled "Name" for a cell that equals "Joe Weinpert"
   
4. Then change a column named "City" in the same row to "Cleveland"
   

Cake, baby!

 

So how does Macro Express fit in? Simple! Look at the changes:

 

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%T1%;Extended Properties=""Excel 8.0;HDR=Yes;"";" 
objRecordset.Open "Select * FROM [%T2%$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText

strSearchCriteria = "Name = '%T3%'"
objRecordSet.Find strSearchCriteria
objRecordset("City") = "%T4%"
objRecordset.Update
objRecordset.Close
objConnection.Close

Notice the Macro Express variables? Create a macro to set variables using prompts:

• T1 = "X:\Data\Addresses.xls"
  
• T2 = "Main"
  
• T3 = "Joe Weinpert"
  
• T4 = "Cleveland"
  

Then set a string variable, say %T10%, to everything between the CODE tags. Save it out to a file named "NameAddress.vbs" and then run it. It can be run by double-clicking on it via Windows Explorer or it can run via the Macro Express Launch Program command.

 

Want more flexibility? VBScript files accept parameters. And to top it all off ... scripting is built into your operating system. Nothing to buy. Nothing to install. It's already there.

 

The point is: you can do ANYTHING using Macro Express to control and run scripts. We know. We make our living at this stuff. All possible through Macro Express. Hubba hubba!

 

Have at it.

[paul]

You can retrieve data from an Access database using VB Script. It's therefore not too difficult to construct a vb script that can be "called" from an ME macro.

 

Here's a simple example (you'll need to provide a fully qualified Access database, a valid table name in the database, and the path to MeProc in your Macro Express folder). It assumes you've got Jet 4 (installed with Access 2003).

 

1) Create a vb script called C:\Test.vbs, containing this code

Dim MyConn, MyRs, WshShell, RecCount
Set WshShell = WScript.CreateObject("WScript.Shell")
Set MyConn = CreateObject("ADODB.Connection")
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FullyQualifiedAccessDatabasename;"
Set MyRs = MyConn.Execute("SELECT COUNT(*) FROM TableName")
RecCount = MyRs(0)
MyRs.Close
Set MyRs = Nothing
MyConn.Close
Set MyConn = Nothing
WshShell.Run("""PathToME\MeProc.exe"" /AAccessReturn /VN1:" & RecCount)

 

2) Create an ME macro called AccessStart, as follows:

Program Launch: "Test.vbs" (Program Name=C:\Test.vbs")

 

3) Create an ME macro called AccessReturn, as follows:

Variable Restore All Variables
Text Box Display: (Box Content=N1 is %N1% (returned from Access))

 

This is one way to pass information back from Access. It's not my preferred way (I'd use the registry, as WshShell can write directly to the registry), but you can use this as a starting point for something more grand <g>!

 

You call the vb script by running the macro called AccessStart. This macro then terminates. The vb script gets its data from your Access database, then calls the macro AccessReturn via MeProc, passing it the returned data (count of records in your table) in %N1%.

[joe]

Sweet! Thanks OzMan, er, Paul.

[Cory]

Wow, you guys are golden gods. <g> Where to start…

 

You confirmed my suspicions that it can be done and it looks fairly lean. I can’t understand the code in your script but it doesn’t look too complicated and I half understand the rest. My feeling is that I can’t simply borrow the script and modify it. Normally if I try to do this I get one little piece of syntax wrong and it won’t work so I think I would need a functional understanding. Otherwise I would rely on you all to write it for me which isn’t fair. Unless I could hire one of you. But if that were to happen I would want more than the script in the way of a mini education. I hate relying on things I don’t understand.

 

So given I don’t know the difference between VB, VBA and VBA and the last language I programmed in was Pascal on a Digital VAX VMS8600 where would I start? Is there some cool online training I can subscribe to or some cool book? I’ve been wanting to learn VB for a long time but I haven’t had a need yet. And how much do I need to know to get done what I need for now?

 

What is WshShell? What is MeProc?

 

I don’t want to pester you script gods but I would appreciate it if you could point me in the right direction to learn so that I can make use of your posts. If you are interested in helping prior to gaining this education I would be willing to hire if inexpensive enough. If you’re like attorneys or Db programmers who get $100+ per hour forget I said anything. <g>

[paul]

So given I don’t know the difference between VB, VBA and VBA

VB is a full-blown stand-alone language, and belongs within Visual Studio.

 

VBA comes with various applications, e.g. Word, Access, Outlook, etc. It's pretty similar to VB, although there are some things you can't do, e.g. build custom ActiveX controls).

 

VB Script is a severely cut-down version of VB, e.g. you can't make Windows API calls, you can't compile it, you can't declare strongly typed variables (e.g. Dim Myvar as Long)

 

and the last language I programmed in was Pascal

Delphi is a popular and powerful Pascal-based language and application development tool.

 

I’ve been wanting to learn VB for a long time but I haven’t had a need yet. And how much do I need to know to get done what I need for now?

How long is a piece of string?

 

Develop a basic understanding of COM or ActiveX (which is the main technique you need to master when communicating with Access databases).

 

Read the Macro Express Help documentation (it's mostly very good), and get Joe's book (it's completely excellent)

 

Get a book on VB Script (I've got quite a good one called "Windows Scripting Secrets" byTobias Weltner).

 

 

What is WshShell?

"The shell is the component of Windows that presents users with an interface to the operating system and provides familiar elements of the Windows desktop experience, including Windows Explorer, the Start menu, shortcuts, and desktop themes.

The WshShell object gives your scripts the ability to work with the Windows shell. Your scripts can use the WshShell object to perform a number of system administration tasks, including running programs, reading from and writing to the registry, and creating shortcuts."

 

Take a look at:

http://msdn2.microsoft.com/en-us/library/ms950396.aspx

and

http://freenet-homepage.de/gborn/WSHBazaar/WSHBazaar.htm

 

What is MeProc?

It's part of Macro Express, and is documented in the Help.

 

I would be willing to hire if inexpensive enough. If you’re like attorneys or Db programmers who get $100+ per hour forget I said anything. <g>

OK, duly forgotten <g>!

[Cory]

Excellent response Paul. Thanks very much. I'll have to take a look at all this on my next weekend moring with a cup-o-joe and hte laptop.

[woodworks]

Paul, your post says that VB Script can not make Windows API calls, but I was wondering if it can make calls to other API's?

 

I have been using Macro Express to automate a cad program that has support for COM and have been experimenting with Visual Basic 2005 Express and the cad API. Impressed with the added capabilities of the VB Script included in macros, I was thinking about trying to incorporate some of my cad API calls into VB Script to do simple tasks.

 

I found your post while doing reseach on this idea, and it does not look encouraging. I learned to program back in the days of Fortan and punch cards, and only a rookie trying to learn this new way of programming using Visual Basic.

 

My current mode of operation with Macro Express is to run one of my compiled VB programs and then get the data that the VB program saved to a file. This forum help get me going with this technique.

[paul]

Paul, your post says that VB Script can not make Windows API calls, but I was wondering if it can make calls to other API's?

I suspect not.

However, you might like to investigate an outstanding freeware scripting product called AutoIt3, which certainly does support API calls via DllCall. While it might seem like a competitor to Macro Express (and certainly there's a lot of overlapping functionality), I think ME is far easier for most people to utilize than AutoIt3.

 

Here's the URL:

http://www.autoitscript.com/autoit3/index.php