Command line query to Access db?

AuntPeggy

AuntPeggy

Final Approach
PoA Supporter
Joined
May 23, 2006
Messages
8,479
Location
Oklahoma
Display Name
Display name:
Namaste
Is it possible to run a select query and an update query to an Access database from the commandline without opening Access?

For testing purposes, I need something like this:
Select specialID from images where imageLocation like '%venus'%;
Update images set imageLocation = 'C:mars' where specialId = 3;
 
I've never done it, but you should be able to ODBC into an Access DB. From there to the command line should be relatively trivial (a VBScript maybe?)
 
ReverendSlappy said:
I've never done it, but you should be able to ODBC into an Access DB. From there to the command line should be relatively trivial (a VBScript maybe?)
Click to expand...

That should work - but bear in mind, whatever machine makes the ODBC call *must* have Access installed because Access is the DB engine that processes the request that passes through the ODBC layer.
 
Greebo said:
That should work - but bear in mind, whatever machine makes the ODBC call *must* have Access installed because Access is the DB engine that processes the request that passes through the ODBC layer.
Click to expand...

Ah. Makes sense. Kinda lame, but makes sense. :D
 
ReverendSlappy said:
I've never done it, but you should be able to ODBC into an Access DB. From there to the command line should be relatively trivial (a VBScript maybe?)
Click to expand...

Greebo said:
That should work - but bear in mind, whatever machine makes the ODBC call *must* have Access installed because Access is the DB engine that processes the request that passes through the ODBC layer.
Click to expand...
Thanks, but I'm still at a loss. What would my DOS batch file look like? It is in ODBC but I'm not sure Access is always installed.
 
AuntPeggy said:
Thanks, but I'm still at a loss. What would my DOS batch file look like? It is in ODBC but I'm not sure Access is always installed.
Click to expand...

It won't be possible in a batch alone... It'll require a VBScript or similar. In the case of a VBScript, running it would then look something like "cscript.exe <script name.vbs> [args]"

And no Access is a deal-breaker; just isn't possible without it (I don't think.)
 
I must use a DOS command line or a DOS batch script. (Don't ask, FDA requirement.)
 
hmmm, FDA requirements, Access db ... among other things ... take a look at software from Aegis Analytical - I used to do installs for them. Was called Discoverant at that time.

http://www.aegiscorp.com/
 
TangoWhiskey said:
Can your DOS batch script call cscript to run a VBS file?
Click to expand...
Nope. Any executable that is called must be validated, which is a cumbersome process that must be done with every test cycle iteration. Microsoft products such as DOS do not need to be validated. We prefer to spend the time testing our own application instead of 3rd party software. (I told you not to ask.)
 
AuntPeggy said:
Nope. Any executable that is called must be validated, which is a cumbersome process that must be done with every test cycle iteration. Microsoft products such as DOS do not need to be validated. We prefer to spend the time testing our own application instead of 3rd party software. (I told you not to ask.)
Click to expand...

Yikes.

That's bad news, because as far as I know, there's just no DOS command that's going to allow you to get into an MDB. No matter what, you're going to have to either run a script or use another executable (think something equivalent to osql) to get at it.
 
AuntPeggy said:
Nope. Any executable that is called must be validated, which is a cumbersome process that must be done with every test cycle iteration. Microsoft products such as DOS do not need to be validated. We prefer to spend the time testing our own application instead of 3rd party software. (I told you not to ask.)
Click to expand...

cscript is a Microsoft product; comes with every install of Windows (just like "DOS"). http://technet.microsoft.com/en-us/library/bb490887.aspx Just allows you to access
another Microsoft scripting language.
 
AuntPeggy said:
Nope. Any executable that is called must be validated, which is a cumbersome process that must be done with every test cycle iteration. Microsoft products such as DOS do not need to be validated. We prefer to spend the time testing our own application instead of 3rd party software. (I told you not to ask.)
Click to expand...

Then they have to have a fine line between what's a Microsoft OS and what's a Microsoft application. cscript/wscript is for all intents a(n optional) part of Windows. You're saying Windows is not OK but DOS is. :dunno:

/x macro Starts Access and runs the specified Access
macro. Another way to run a macro when database or
you open a database is to use an Access
AutoExec macro.

http://support.microsoft.com/kb/209207
Click to expand...

Doesn't solve your problem if you don't have Access on the client.
 
You guys are the greatest! I have followed up on CScript. (I'll ask our experts tomorrow to see whether we can use it. Since it is distributed with DOS, I expect it is OK to use.) I'm an old UNIX hacker (and Mac), so not up-to-date on DOS.

Here is the script I have written.
Code: 
On Error Resume Next
 
Const adOpenStatic = 3
Const adLockOptimistic = 3
 
Set objConnection = CreateObject( "ADODB.Connection" )
Set objRecordSet  = CreateObject( "ADODB.Recordset" )
 
objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Documents and Settings\Owner\Desktop\Test.mdb"
 
objRecordSet.Open "SELECT specialId FROM images " & _
    WHERE imageLocation like '%venus%'" , _
    objConnection, adOpenStatic, adLockOptimistic
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    myId = objRecordSet.Fields.Item("specialId")
    Wscript.Echo myId
    objRecordSet.MoveNext
Loop
objRecordSet.Close
 
objRecordSet.Open "UPDATE images SET imageLocation = 'C:\mars' " & _
    "WHERE specialId = '" & myId & "'", _
    objConnection, adOpenStatic, adLockOptimistic
 
Last edited:
AuntPeggy said:
You guys are the greatest! I have followed up on CScript. (I'll ask our experts tomorrow to see whether we can use it. Since it is distributed with DOS, I expect it is OK to use.) I'm an old UNIX hacker (and Mac), so not up-to-date on DOS.

Here is the script I have written.
Code: 
On Error Resume Next
 
Const adOpenStatic = 3
Const adLockOptimistic = 3
 
Set objConnection = CreateObject( "ADODB.Connection" )
Set objRecordSet  = CreateObject( "ADODB.Recordset" )
 
objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Documents and Settings\Owner\Desktop\Test.mdb"
 
objRecordSet.Open "SELECT specialId FROM images " & _
    WHERE imageLocation like '%venus%'" , _
    objConnection, adOpenStatic, adLockOptimistic
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    myId = objRecordSet.Fields.Item("specialId")
    Wscript.Echo myId
    objRecordSet.MoveNext
Loop
objRecordSet.Close
 
objRecordSet.Open "UPDATE images SET imageLocation = 'C:\mars' " & _
    "WHERE specialId = '" & myId & "'", _
    objConnection, adOpenStatic, adLockOptimistic
Click to expand...

Just took a quick glance, but yeah that looks like it should work. And I can't really imagine them having a problem with Windows Scripting Host (which is what CScript is a part of) but then ya never know, I suppose.

The bigger question is whether or not they'll have a problem with Access (or the Access runtime) being installed...
 
What slap said. Even if you never see an Access window, the Access runtime has to be installed. When you access an Access database by any means, the access runtime is what actually manipulates the data.

I'm rather stunned the FDA is using Access, period. Access is *not* an enterprise level database platform and shouldn't be used as one... but people will insist on trying...
 
Greebo said:
What slap said. Even if you never see an Access window, the Access runtime has to be installed. When you access an Access database by any means, the access runtime is what actually manipulates the data.

I'm rather stunned the FDA is using Access, period. Access is *not* an enterprise level database platform and shouldn't be used as one... but people will insist on trying...
Click to expand...

I have been stunned -- I mean really, really stunned -- at what some folks decide to run on Access. Paraphrasing a few conversations I had a few years ago:

Business Dude: "Hello, Matt... We have a problem with this 800MB Access database. Can you help us out?"

Me: "Wow that sucks. But I've got kind of a lot going on... Is the person who created it available to help you?"

Business Dude: "Um... Well... Not exactly 'available', no. It was created about 8 years ago, and the person who created it no longer works here. Actually, he's dead and we're not really sure he even, uh, exactly 'worked here' in the first place."

Me: "Hmm... Can I swing by later this week?"

Business Dude: "Well, sooner than that would be better. The thing is we, like, can't really process any cash transactions without it."

Me: "You're dumb."
Click to expand...

Okay, well I didn't really say that last part and I'm exaggerating a tiny bit, but...

Like I mentioned in another thread: If the business case for a tech project doesn't include the phrase "the entire planet will explode and you will die a slow, painful death if we don't do it" as part of the value proposition, it's really hard to get some folks to lay down the coin to do what needs to be done -- and done The Right Way(tm). Edit: And in most cases, those same folks consider an Access solution to be "good enough". And the problem is that it is... Until it's not anymore.
 
Last edited:
Greebo said:
What slap said. Even if you never see an Access window, the Access runtime has to be installed. When you access an Access database by any means, the access runtime is what actually manipulates the data.

I'm rather stunned the FDA is using Access, period. Access is *not* an enterprise level database platform and shouldn't be used as one... but people will insist on trying...
Click to expand...

ReverendSlappy said:
I have been stunned -- I mean really, really stunned -- at what some folks decide to run on Access. Paraphrasing a few conversations I had a few years ago:



Okay, well I didn't really say that last part and I'm exaggerating a tiny bit, but...

Like I mentioned in another thread: If the business case for a tech project doesn't include the phrase "the entire planet will explode and you will die a slow, painful death if we don't do it" as part of the value proposition, it's really hard to get some folks to lay down the coin to do what needs to be done -- and done The Right Way(tm). Edit: And in most cases, those same folks consider an Access solution to be "good enough". And the problem is that it is... Until it's not anymore.
Click to expand...

I have no idea if FDA uses Access. We use it to store locations of test images (and their attributes) when we test medical imaging software for some unit tests. I need to manipulate the location information within the database to be sure my software can get to those images wherever they are. We are in the process of automating a manual test where the tester had no problem making a temporary change to the location in two tables of the database.

OTOH, Access is a very good tool for limited amounts of data and I would be surprised if it is not used on desktops in nearly every agency of the government.
 
ReverendSlappy said:
Just took a quick glance, but yeah that looks like it should work. And I can't really imagine them having a problem with Windows Scripting Host (which is what CScript is a part of) but then ya never know, I suppose.

The bigger question is whether or not they'll have a problem with Access (or the Access runtime) being installed...
Click to expand...

The problem is - and I'm in that box - your clients have to have WSH installed, and as you say Peggy's clients need Access installed.
 
Peggy, Don't get me wrong. Access is an excellent tool when used for its intended purpose. It's a great little rubber mallet. The problem happens when people (at a corporate enterprise level) try to use it as a jackhammer instead.
 
Greebo said:
Peggy, Don't get me wrong. Access is an excellent tool when used for its intended purpose. It's a great little rubber mallet. The problem happens when people (at a corporate enterprise level) try to use it as a jackhammer instead.
Click to expand...

What he said. :D
 
I know there is a ton of Access data running around out there in the environment under FDA purview... way more than I would be comfortable with, but not surprised by it, either.

The company and software that I linked to above provides data access into and analytical functions on, among other things, cradle to grave tracking for pharma and bio-pharma manufacturing, with hooks into every system along the way, from raw materials to finished product.
 
ah, the old "executable" issue with the Feds (I lived with it for years when working for Another Fed Acronym)...consider Perl or PHP. Interpreted source code adheres to the rules of no executable (binary code is the true issue - Feds don't trust anyone, including its own employees much less contractors) with ODBC connectivity.

You may also be able to get away with Java (thin client calls thru ODBC) but Perl already has the packages built. Check out

search.cpan.org/~jurl/DBD-ODBC-1.13/ODBC.pm

which includes an example of talking to an MS Access DB.
 
gkainz said:
I know there is a ton of Access data running around out there in the environment under FDA purview... way more than I would be comfortable with, but not surprised by it, either. ...
Click to expand...

The FDA is the least of it. Diebold was (is?) running Windows and Access on the electronic voting machines - you know - the ones that didn't require any printers or audit trails.
 
You must log in or register to reply here.
Back
Top