Running Access SQL using Javascript

  • Thread starter Thread starter Guest
  • Start date Start date
Two questions:

1. Can it be done?
Click to expand...

Yes, if JavaScript is OLE-aware, which I believe it is.
2. How?
Click to expand...

These sample scripts are in VBScript and Perl but should give you the
idea. They both use the Jet database engine directly rather than
automating Access itself: that lets you run most queries. You can also
automate Access but it's more complicated: see
http://support.microsoft.com/?id=210111

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access

'Modify DB_NAME, TBL_NAME, DATA_SOURCE as required
'and the code that builds strSQL as necessary.

'If TBL_NAME exists, appends to it; otherwise creates it.

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const TBL_NAME = "My_Table"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Temp\;].B1#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL, 128 'dbFailOnError

oDB.Close
'END

#Perl
use strict;
use Win32::OLE;

my $Jet; #DAO.DatabaseEngine
my $DB ; #DAO.Database

my $SQLquery = "INSERT INTO Details (SaleID, Comment)
VALUES (11, 'Test value from Perl');";

$Jet = Win32::OLE->CreateObject('DAO.DBEngine.36')
or die "Can't create Jet database engine.";

$DB = $Jet->OpenDatabase('C:\\Temp1\\BoxWithinBox_Backup.mdb');

$DB->Execute($SQLquery, 128); #128=DBFailOnError

$DB->Close;
#END
 
Back
Top