Automatic data input

[Andrew]

I import data to access from a comma delimted text file.
The data logging program produces 1 log file everyday. Is
there a way to automate the importing of this data into
access, rahter than having to open the database and import
the log file everyday.

For example I could use a simple batch file possibly

c:\asdddd\access.exe /some switches
move c:\logs *.* c:\logs\have_been_imported

where the switches tell access to import the text files
etc.
or something similar

 

[John Nurick]

Hi Andrew,

The /x commandline switch lets you specify a macro to run automatically.
So the thing to do is something like

1) Add a linked table to your database, linked to a sample log file.

2) Create a VBA procedure that
a) gets the name of the new log file
b) modifies the .Connect property of the TableDef object corresponding
to the linked table, so it points at the file
c) runs an append query to add the data from the linked table to your
main table.
d) if the import is successful, moves the file to the have_been_imported
folder
e) adds a record to an audit trail table with the file name and whether
the import was successful.

3) Create a macro that calls the VBA procedure.

4) Create a batch file that calls Access. The command will something
like this, all on one line (if there's no security on the database you
won't need the workgroup file):

"C:\Program Files\Microsoft Office\Office\msaccess.exe"
"D:\folder\folder\database.mdb" /wrkgrp "E:\folder\security.mdw"
/xMyMacro

 

[John Nurick]

Hi Stan,

Probably the simplest way to do this is to ignore Access and use DAO to
manipulate the back end mdb file directly. You can use the DAO object
model from just about any OLE-compatible language or application. Here's
a sample procedure that opens a secured mdb and will work with minor
modifications in VBScript, VB and VBA.

Dim dbE 'As DAO.DBEngine
Dim dbW 'As DAO.Workspace
Dim dbD 'As DAO.Database
Dim rsR 'As DAO.Recordset

Set dbE = CreateObject("DAO.DBEngine.36") 'VBS, late binding
'Set dbE = New DAO.DBEngine 'VB/VBA, early binding

dbE.SystemDB = "C:\temp\xx\secured.mdw"
Set dbW = dbE.CreateWorkspace("Test", "Amoss", "Elsie")
Set dbD = dbW.OpenDatabase("C:\temp\xx\test.mdb")

Set rsR = dbD.OpenRecordset("RESULTS")
With rsR
MsgBox .Fields(0).Value 'demo only
'Add a record:
' .AddNew
' .Fields(FileName).Value = strResultsFileName
' ...other fields if needed...
' .Update
End With

rsR.Close
dbD.Close
dbW.Close
Set rsR = Nothing
Set dbD = Nothing
Set dbW = Nothing
Set dbE = Nothing

I have found this discussion relevant to a similar task I am trying to automate.

In my case I have a database with a running list of RESULTS filenames
from a CNC machine. Every time a new RESULTS file is generated by the
CNC machine's software, I would like an identifier matching the RESULTS
filename added to the RESULTS table in my database. I have the facility
to shell out of the CNC machine's software and call VBA code.

I expect I won't need to necessarily link to any log file, but run some code that

determines the string representing the RESULTS file and then simply
adds a single new record to a query involving fields in several tables.

 

[Guest]

John

Very interesting. This started out as mere dabbling with Access and VB for me. Now wer'e passing the expert stage at the office and getting into guru. If only my peers new my real secret: this newsgroup

Thanks

 

[Guest]

John

I am in over my head on this one. I am aware of a Visual Basic 6.0 program from Microsoft, but wouldn't want to tinker without knowing I'm looking at the right platform. Could you advise as to possible choices for the "OLE-compatible language or application" you speak of

I would need our Office Software administrator to OK any custom installs, ie. extra add-ins, so this will give me the ammunition for a special request

Thanks

Stan Schnuerer

 

[John Nurick]

Hi Stan,

You said in your first post

In my case I have a database with a running list of
RESULTS filenames from a CNC machine. Every time a
new RESULTS file is generated by the CNC machine's
software, I would like an identifier matching the
RESULTS filename added to the RESULTS table in my
database. I have the facility to shell out of the
CNC machine's software and call VBA code.

and I assumed that the last sentence meant that the CNC software could
execute VBA code like the example procedure I posted. If it can't, I
don't know what you mean by "shell out ... and call VBA code".

If you mean "shell out" in the usual sense of being able to pass
commands to the Windows shell: you can't work with libraries such as DAO
directly from the shell, you have to create a program or script that
does the work and use a shell command to call that. Almost any common
Windows programming or scripting language can do this, including
VBScript (which is probably installed on your workstation although your
administrator may have disabled it), Visual Basic, Perl, Python, Delphi
and many more. The code I posted works in VBScript and should work with
minimal modification in Visual Basic.

John,

I am in over my head on this one. I am aware of a Visual

Basic 6.0 program from Microsoft, but wouldn't want to tinker without
knowing I'm looking at the right platform. Could you advise as to
possible choices for the "OLE-compatible language or application" you
speak of?

I would need our Office Software administrator to OK any custom

installs, ie. extra add-ins, so this will give me the ammunition for a
special request.