How to link to Access data

[Cory]

I have several macros that rely on data stored in an Access database. At the moment I export special queries that pump the data into a tab separated values file for ME which is obviously silly because it requires someone to manually jump thru a series of hoops every time something is changed in the Db. This morning I plan on writing a solution but I hope you can give me suggestions. Oh, and I fully accept that the suggestions might not involve ME directly.

 

I have a few thoughts on how to do this so I’ll toss them out here for comment:

1. Read directly from the Db with vb script: Obviously the best method is not to export at all but link to the data. I know VBScript can tap Access databases but this one is secured with a WIF (Workgroup Information File) which requires every user to log in with a password in order to access the Db. Is there a way to use VBScript in this case?

2. Link queries or tables to a text table: I know one can link to text files and they appear as tables in Access but I’ve never experimented with them. Probably will this morning.

3. Export manually using VBA: Might be a simple way to have a user button in the Db GUI where a user can push a button and all would be exported.

4. Export with ME: As a last resort I can make the queries appear, copy them with the clipboard and run with them using ME. Probably the easiest method and I know I can do it quick but… Ick!

 

I know a lot of you do databasey things all the time with ME so I’m hoping that this is not an uncommon problem and I can learn form other’s experience. Thanks!

[joe]

From what I'm reading, you have an Access database that contains data required by some macros in order to run. Furthermore, this data changes periodically, and when it does, you need to run these macros again using the new data.

1. Is this correct?
   
2. Do these same macros ever need to write the data back?
   

[Cory]

1. Ummmm Yesss… Sort of. Understand that there is no macro currently to do this, only macros that require those text files to be current. Furthermore I am not resigned that a Macro Express macro will be the solution. Let me make an example. I have table of clients and a folder structure that has a file name convention consisting of the client name and client ID. My macro uses the exported tab file to audit the client folder names. There’s a lot more but this is conceptually simple. In this example my file audit macro might be modified to use a VBScript to look directly into the database. Or I create a text file link in Access that gets updated automatically. Or I write a VBA macro in Access. Or I write a new ME macro to export whenever a user makes a change.

2. No. Referential only.

 

Clear as mud?

[Cory]

Method 2 is out. Linking tables is a one way street making the data, for all practical purposes read-only.

Method 3 VBA Docmd.TransferText looks promissing though.

 

Sure wish there was a way that didn't require a use to push a button though...

[joe]

1. Ummmm Yesss… Sort of ... Clear as mud?

Well, yes, it's clear as mud. What I'm thinking is it almost sounds like you could query the Access table for information when you need it as Macro Express is processing the data. Does this sound correct?

 

This can be done with VBScript and ADO commands. Let me point out however, that we've done this sort of thing with Excel, but never with Access. I can't imagine, though, that it would be that different.

[Cory]

Yeah, ideally I think peaking into the Db when the macro in example goes to audit folder names or whatever. This means it's always looking at the most recent data. The problem I'm anticipating is that the MDB file is secured with a WIF. I got more questions along this line as to the practicalities but I'll stop here. Can VBS query an MDB table that has been secured?

 

In Excel I access the data in the DB but I can't do it directly and have to set up an OBDC link with the credentials. It's fine for a user or two but I don't want to have to do this for the entire company. Seems one should be able to do it thru the API.

[joe]

Can VBS query an MDB table that has been secured?

I do not know, but the ADO Open command (which you would use as part of the script), allows for a User ID and Password.

[Cory]

OK Pandora, you asked for it.

 

Assuming for the moment this might be viable what kind of data would it return? I mean does it return the contents of the object in one variable or is there some way to plow thru records as we might do with ASCII File Repeat?

 

I’m not sure about the viability of this for security reasons. If it’s a distributed macro I would need a way to…. Well, I suppose I could keep the user name and password in the local user’s preferences in the registry. I just don’t like the idea of them all using the Admin account all the time. But then again it’s only referential so I guess there’s no harm. But being able to access data real time would be so cool I could live with it if need be.

 

So do you think you could give me a simple sample to play with?

[joe]

Pandora? Ah, Zeus' punishment to mankind for obtaining fire ... the first woman. Hey! c'mon everyone, relax. It's just a fable, right?

 

does it return the contents of the object in one variable or is there some way to plow thru records as we might do with ASCII File Repeat?

In your case, yes. I say that only because it appears that you have no problem using the Macro Express's file processing commands, and that's great. They do a real fine job, and I prefer them (mostly) over internal scripts. In reality, ADO returns SQL-style record sets. However, a script can convert them to CSV or text files easy enough.

 

So do you think you could give me a simple sample to play with?

Yes and no. I can give you something to wet your appetite for Excel, but not Access. Maybe, you could take it from there. Or if someone else here has any Access database experience, that would be great, too!

[Cory]

Who was it that was speaking about doing Db queries every morning? I think it was Paul, maybe he will chime in.

 

For the record I don’t like using the ASCII File Process command unless I have to. If I have a chunk of tabular data like one might copy form an Excel worksheet I would rather chew thru it tab by tab in a string var. I don’t know what you mean by a SQL style dataset. I imagine a large chunk of data delimited in some fashion like the aforementioned Excel copy. If this is the case this is EXACTLY what I am looking for.

 

Having said that I think that if I had to request the data in a more specific way that might actually make things easier. In access if I write a certain query and get a null set it tells me this doesn’t exist. If my macro I the given example were to query the Db and ask for “XYZ Corporation” and the response was a big fat “No” then that would work even better. Or, as a better example, query the Db for the client ID number for XYZ things would get a lot simpler and faster.

 

How do you pass the results of a VBScript back to ME?

 

I know you are working on a funtions library for Excel but you might consider expanding it in a like fashion to Access.

[floyd]

For the record I don’t like using the ASCII File Process command unless I have to. If I have a chunk of tabular data like one might copy form an Excel worksheet I would rather chew thru it tab by tab in a string var.

I'm curious as to why you don't like using the ASCII File Process command. Doesn't it do exactly what you are doing? That is: it chews through the data tab by tab.

 

 

I don’t know what you mean by a SQL style dataset. I imagine a large chunk of data delimited in some fashion like the aforementioned Excel copy. If this is the case this is EXACTLY what I am looking for.

And you are exactly correct.

 

 

How do you pass the results of a VBScript back to ME?

It's almost certain that Joe is referring to extracting a range of data and copying it to a CSV file for MX3 to process. Maybe some sort of sequence like this:

 

Macro Express command
Macro Express command
Macro Express command
Run extraction script
Wait for extraction script to finish
Macro Express processing command
Macro Express processing command
Macro Express processing command

 

I know you are working on a functions library for Excel but you might consider expanding it in a like fashion to Access.

O we are! We are! But after the Excel Library comes the Internet Explorer Library.

[Cory]

I was evaluating all solutions in parallel and found that the VBA export was so simple even I could do it so implemented it as the current solution. DoCmd.TransferText acExportDelim ended up doing exactly what I needed in one command. Now I have one button the user clicks that dumps several Tab files. This is a huge improvement over having a user manually export several queries with wizards but I do still want to pursue a direct link as the final solution.

 

I don’t like using the ASCII File Process command

I'm sorry, I didn't explain myself well. I do like the ASCII file process and if this is how the VBScript would pass the data back that would be great. What I meant was... Well an example would be a clipboard copy of a chunk of Excel cells. Once in a string var I would rather chew thru it in memory than write it to the hard drive and run the ASCII File Process. Also when looking up data in such tables it's a lot faster to plow thru data in RAM than the HDD. Also if I am generating a results file I will build the file contents in RAM with tabs and CRLF and make one write to the disk instead of thousands. It's just that in some cases reading and writing from the HDD can really slow things down. If the VBScript could pass this query results back to ME somehow in RAM, like the CLIP.exe, I would rather go that route than writing it to disk. But if generating a CSV file is the only way then I'm all over it. Does that make more sense?

 

So do you have examples I might be able to try? Or should I just wait for the Functions Library to be expanded to Access?

[paul]

Who was it that was speaking about doing Db queries every morning? I think it was Paul, maybe he will chime in.

 

Ding, ding!

 

Your question has 2 parts:

1) How to connect to an Access database secured using Workgroup Security

2) How to communicate the results back to ME

 

Here's some VB Script code to achieve this (using MS Access 2003); I'm indebted (not for the first time!) to guru Garry Robinson for the connection string values (http://www.vb123.com/toolshed/02_docs/accessadosecure.htm):

Dim MyConn, MyRs, WshShell, RecCount

'Creates the ADO connection
Set WshShell = WScript.CreateObject("WScript.Shell")

Set MyConn = CreateObject("ADODB.Connection")
With MyConn
 .Provider = "Microsoft.Jet.OLEDB.4.0;"
 .Properties("Jet OLEDB:System database") = _
"C:\Documents and Settings\pault\Application Data\Microsoft\Access\SystemTest.mdw"  'Fully qualified workgroup security file name
 .Properties("Password") = "xxxxx"   'password for authorized user
 .Properties("User Id") = "Test" 'authorized user
 .Open "Data Source=" & "G:\Ceru\CeruMensHealth.mdb;"  'Fully qualified database name
End With  

Set MyRs = MyConn.Execute("SELECT COUNT(*) FROM Person")  'A valid query that returns a result (more on this below)
RecCount = MyRs(0)

'It's vital to clean up properly after using ADO, otherwise you may experience memory leaks
MyRs.Close
Set MyRs = Nothing
MyConn.Close
Set MyConn = Nothing

WshShell.Run("""E:\Program Files\Macro Express3\MeProc.exe"" /AAccessReturn /VN1:" & RecCount) 'return the singleton result to ME

This example shows one way of returning a result (singleton) to ME. Another way would be for your macro to launch the script and use a Repeat loop to wait for the result (e.g. initialize a registry value to -1, change the script above to write the result into this registry value, loop in ME until the registry value is other than -1).

 

Returning a recordset to ME is harder, since ME can't deal with arrays. I'd probably make the .vbs script loop through the recordset and write it out to a text file (or use DoCmd.TransferText), then process that text file in ME (even though you say you don't like this approach). And the registry value returned from the script could indicate (e.g. value=-2) that some other registry value contains the name of the file it's just created for you.

 

You're limited only by your imagination as to what you can do.

[joe]

WshShell.Run("""E:\Program Files\Macro Express3\MeProc.exe"" /AAccessReturn /VN1:" & RecCount) 'return the singleton result to ME

Nice touch Paul. Very good. Very good, indeed!

[Cory]

Woah. I need to sit down. Wait, I am sitting down... Why is the room spinning?

 

This is terrific! But like an archeologist discovering the remnants of a lost society it might take me a while to make sense of it. Unfortunately I have some chores to attend to first today but I can’t wait.

 

OK, OK, I LIKE ASCII Repeat! No really, I do. For things like this it would be fine. I just avoid it when it’s going to cost the user twiddle time.

 

I know this is premature and I should just study this myself but is there a way I could, in effect, get a query result? For instance in a simple table could one ask nicely to simply return the client name associated with a certain client ID? I’m guessing there are a lot more capabilities which mean my whole approach in the macro should be different. As it is now I use ASCII FP to plow thru the entire exported client file until I find a client ID match then grab the client name in the next string variable over. Hmmmm…. My mind boggles. I had better post this before I go overboard...

[floyd]

I could, in effect, get a query result? For instance in a simple table could one ask nicely to simply return the client name associated with a certain client ID

Yes, but because this is Access maybe Paul could show you how. Access is not what we do. Excel is. Anyway, Paul's example above is very close to what you need. The Select command would change, as would the value being returned.

 

As a side note, you will see this capability in our Excel Extended Library when we get to the "range" functions. Also, the ability to return a range in a csv format.

[Cory]

Thanks Floyd. I can't wait to get into this and play around.

[paul]

I know this is premature and I should just study this myself but is there a way I could, in effect, get a query result?

The term "query result" of course means a singleton result like "How many people qualify?", OR a single row, like "What is the name and address of my best customer?", OR many rows, like "What are the names and addresses of my top 10 customers?"

 

How would you deal in ME with the output from the last 2 types of queries?

 

I'm going to try out the performance of an alternative solution to returning results to ME.

ME is going to communicate with the VB script via 3 registry values, Numeric1, String1 and String2.

Numeric1 value will represent a status flag:

1 = Returned data from VB Script

2 = VB Script has finished returning data

-1 = Data requested by ME

-2 = No more data requested by ME

 

String1 will represent a row of data from the query run by VB Script (each column being delimited by /)

String2 will represent EITHER the name of a query, or the SQL code needed for the query

 

ME
 set string2 to query or query name
 write Numeric1 = -1
 launch vbscript  
 repeat until n1 = 2
repeat until n1 <> n1
  read Numeric1
  if Numeric1 > 0 repeat exit
repeat end
if Numeric1 = 2 repeat exit
read string1
process string 1
write Numeric1 = -1 (or -2 if we don't want any more data)
 repeat end

VBScript
 initialize
 run query
 do until RS.EOF
write String1 = RS row (delimit columns with /)
write Numeric = 1
do until Numeric1 < 0
loop
if numeric1 = -2 exit do
 loop
 cleanup

Without having tried it, I don't see why this wouldn't work well.

[paul]

Well, it was easier than I'd thought and it responds quickly.

ME Macro
-----------

<REM2:Disable this line if you want to get all the data from the query><REM2:Otherwise data is returned until the first 2 characters = 13><IVAR2:10:01:1><REM2:CrLf to separate returned rows in final display><ASCIIC:10:1:13><ASCIIC:1:1:10><TMVAR2:08:10:01:000:000:><REM2:Query><TVAR2:02:01:SELECT StatusId, StatusDesc FROM Status WHERE DatabaseId = 1 ORDER BY StatusId><REGWSTR:2:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ParameterString1><REM2:More Data please><IVAR2:01:01:-1><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><REM2:The VB Script><LAUNCHDEL2:0:01D:\TestAccess.vbs1><REM2:The Loop><REP3:08:000002:000002:0001:1:01:N1><REM2:Wait for the request><REP3:08:000004:000002:0001:0:01:0><REGRINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><ENDREP><IFVAR2:2:01:1:2><REM2:We're done><EXITREP><ENDIF><REM2:Get the data><REGRSTR:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1><REM2:Save it><TMVAR2:08:11:01:000:000:><REM2:Append CrLf><TMVAR2:08:11:10:000:000:><REM2:If N10 = 1 then we'll stop early><TMVAR2:10:02:01:001:002:><IFVAR2:2:10:1:1><AND><IFVAR2:1:02:1:13><REM2:No More Data please><IVAR2:01:01:-2><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><EXITREP><ELSE><REM2:More Data please><IVAR2:01:01:-1><REGWINT:1:HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnInteger1><ENDIF><ENDREP><REM2:Display all the output><TBOX4:T:1:000379000105000702000769:000:Returned data%T11%>

 

VB Script
-----------

Dim MyConn, MyQuery, MyRs, WshShell
Dim intI, Output, Response 

'Creates the ADO connection
Set WshShell = WScript.CreateObject("WScript.Shell")

MyQuery = WshShell.RegRead("HKCU\Software\Professional Grade Macros\Parameters\ParameterString1")
Set MyConn = CreateObject("ADODB.Connection")
With MyConn
 .Provider = "Microsoft.Jet.OLEDB.4.0;"
'  .Properties("Jet OLEDB:System database") = _
'	"C:\Documents and Settings\pault\Application Data\Microsoft\Access\SystemTest.mdw"
'  .Properties("Password") = "xxxxx"
'  .Properties("User Id") = "Test"
 .Open "Data Source=" & "G:\Ceru\CeruStudies.mdb;"
End With  

Set MyRs = MyConn.Execute(MyQuery)

With MyRs
 Do Until .EOF
Do Until Response < 0
  Response = WshShell.RegRead("HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1")
Loop
If Response = -2 Then Exit Do
Response = 0

Output = ""
For intI = 0 To .Fields.Count - 1
  Output = Output & .Fields(intI) & "/"
Next
Output = Left(Output, Len(Output) - 1)

WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnString1", Output, "REG_SZ"
WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1", 1, "REG_DWORD"
.MoveNext
 Loop
 .Close
End With

WshShell.RegWrite "HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1", 2, "REG_DWORD"

'It's vital to clean up properly after using ADO, otherwise you may experience memory leaks
Set MyRs = Nothing
MyConn.Close
Set MyConn = Nothing

 

Notes:

- I've disabled the Access database security stuff, simply because I don't have a secured database on my home machine!

- I'm using the following registry values:

* HKCU\Software\Professional Grade Macros\Parameters\ReturnInteger1 (Numeric1 in previous message)

* HKCU\Software\Professional Grade Macros\Parameters\ParameterString1 (String2 in previous message)

* HKCU\Software\Professional Grade Macros\Parameters\ReturnString1 (String1 in previous message)

* If you change these, change the corresponding references in both the macro and script

- The script name is D:\TestAccess.vbs (if you relocate it or change its name, change the corresponding reference in the macro)

- The Access database is G:\Ceru\CeruStudies.mdb (change the drive, path and database name in the script)

- There's no error-checking in the script

- String2 must be valid SQL defining your query; it wouldn't be difficult to allow a query name as an alternative

[Cory]

I'm going to need a cup o' joe for this one. It seems very elegant and powerful but at this point I only have a vague notion what's happening here. I'll have to play with it before I can make much comment. But let me answer your question from 2 posts ago. But before I do please understand I have no idea what the capabilities are for the VBScript but that pretty much looks like a SQL statement to me. My problem is that not knowing what it can do I don't know what the best approach should be. Hold that thought.

 

For your question about how ME would handle a tabular query result I refer you back to Floyd's posts. It seems from what they do in Excel you could end up with a string variables that's delimited with tabs and CrLfs like one might get doing a clip copy from Excel. I could easily chew thru that in ME.

 

Now back to that thought. Let me turn this around for you by giving you a couple of examples of one of my applications and you tell me how you think it would be best to use VBScript.

 

1- Imagine a simple table in access with client IDs and client names. I might want to return the client name associated with the client ID provided.

2- Using the same table imagine that I have a folder full of client folders who has the convention of [Client name] [Client ID] fo rthe name and I need to periodically audit this. It would be nice if I could, in effect, do an ASCII FP with the table contents checking each folder against each record.

 

Does that help any?

[paul]

But before I do please understand I have no idea what the capabilities are for the VBScript but that pretty much looks like a SQL statement to me. My problem is that not knowing what it can do I don't know what the best approach should be. Hold that thought.

As far as VB Script is concerned, it knows nothing, and cares even less, about SQL. I've simply created an instance of an ADO connection and passed in various parameters and instructions, one of which is some SQL code, and another of which is to go and execute that SQL code. Since this SQL code returns a recordset, I further instructed ADO to return that recordset, which I then read line by line. Each line is written to the registry (columns are separated by, in this case, "/", but it could just as well have been a tab character). ME then reads the value in the registry and processes it accordingly. Since a recordset usually contains several rows, I've incorporated a simple protocol to allow ME to communicate with the VB Script (ME: "I need some [more] data", or "I don't want any more data"; VBS: "Here's some more data", and "I'm done"), where each party to the conversations waits politely for the other to finish a sentence before speaking again).

For your question about how ME would handle a tabular query result I refer you back to Floyd's posts. It seems from what they do in Excel you could end up with a string variables that's delimited with tabs and CrLfs like one might get doing a clip copy from Excel. I could easily chew thru that in ME.

My question was not how ME would handle a result set - it was more to do with how you would handle it.

1- Imagine a simple table in access with client IDs and client names. I might want to return the client name associated with the client ID provided.

2- Using the same table imagine that I have a folder full of client folders who has the convention of [Client name] [Client ID] fo rthe name and I need to periodically audit this. It would be nice if I could, in effect, do an ASCII FP with the table contents checking each folder against each record.

Both of these requirements could readily be met with the above code, unaltered in any way (apart from the specification of the query itself).

[Cory]

This is great. I can't wait ot play with it. Thank you very much Paul!