Help With Script Problem

[Guest]

Bob Barrows MVP in VBScripting suggested I ask this here so I apologize for
the borderline multipost in advance.

From a ".VBS" file I am running the following that Bob helped me with:

dim app
set app=creatobject("Access.Application")
app.OpenCurrentDatabase("U:\jeff.mdb")
app.DoCmd.TransferText acImportFixed, "My ImportSpec", _
"Name Of My Table where data will go, _
"Full Path Of Text File To Import"
app.quit acQuitSaveNone
set app = nothing

So, here is the problem. The above runs but imports the data incorrectly.
When I walk through the steps manually in the database, "get external data"
etc etc. and using the correct import spec the data comes in just fine.

No matter what, running the script brings in all the data but incorrectly.

there are four columns, name, acct, date, time.

when imported/appended acct number imports correctly, name and date in coumn
two, column three and four are empty and time is not brought in.

Any thoughts anyone?

Thank you.

 

[Dirk Goldgar]

Bob Barrows MVP in VBScripting suggested I ask this here so I
apologize for the borderline multipost in advance.

From a ".VBS" file I am running the following that Bob helped me with:

dim app
set app=creatobject("Access.Application")
app.OpenCurrentDatabase("U:\jeff.mdb")
app.DoCmd.TransferText acImportFixed, "My ImportSpec", _
"Name Of My Table where data will go, _
"Full Path Of Text File To Import"
app.quit acQuitSaveNone
set app = nothing

So, here is the problem. The above runs but imports the data
incorrectly. When I walk through the steps manually in the database,
"get external data" etc etc. and using the correct import spec the
data comes in just fine.

No matter what, running the script brings in all the data but
incorrectly.

there are four columns, name, acct, date, time.

when imported/appended acct number imports correctly, name and date
in coumn two, column three and four are empty and time is not brought
in.

Any thoughts anyone?

Could you post a sample of the data, or dummy data that matches the
format?

 

[Guest]

Sure Dirk, Thanks:

ACCT# NAME
DOD TOD
H11111111111 Last Name, First Name 5/16/2006 2045
text Text
Date/Time Text

Hope this lends a clue, the empty table that this is being put in has the
same field names and data properties which also match the import spec. It
works just fine when I walk throught it step by step manually.

 

[Dirk Goldgar]

Sure Dirk, Thanks:

ACCT# NAME
DOD TOD
H11111111111 Last Name, First Name 5/16/2006
2045 text Text
Date/Time Text

Hope this lends a clue, the empty table that this is being put in has
the same field names and data properties which also match the import
spec. It works just fine when I walk throught it step by step
manually.

Interesting. I think you've found a bug.

I created a table with that single record, and created an import
specification for it. When I ran the import manually, it worked fine.
When I executed DoCmd.TransferText ... from the Immediate Window, it
also worked fine. But when I ran it externally via a VBScript similar
to yours, it gave me the same skewed result you reported.

I found a workaround, if your circumstances permit you to use it. I
created a public function in the target database, in a standard module.
That function, which I named "ImportIt" for my test purposes, contained
only the DoCmd.TransferText statement, copied from the VBScript file
(but with the "app." stripped from the front). Then I changed my VBS
file to do this:

dim app
set app=CreateObject("Access.Application")
app.OpenCurrentDatabase("<path to database>")
app.Run "ImportIt"
app.quit acQuitSaveNone
set app = nothing

When I ran that VBScript, it worked fine.

 

[John Nurick]

Hi Dirk and Jeff

dim app
set app=creatobject("Access.Application")
app.OpenCurrentDatabase("U:\jeff.mdb")
app.DoCmd.TransferText acImportFixed, "My ImportSpec", _
"Name Of My Table where data will go, _
"Full Path Of Text File To Import"
app.quit acQuitSaveNone
set app = nothing

Unless you've declared
Const acImportFixed = 1
Const acQuitSaveNone = 2
this won't do what it says. In particular, it's probably doing a
delimited import not a fixed-width one. (Another lesson to ALWAYS
declare Option Explicit).

A better scripting approach IMHO would be to replace the import spec
with a schema.ini file (either static or created on the fly by the
VBScript). You could then cut Access out of the loop and just use the
DAO library.

See e.g.
DAO_Execute.vbs at http://www.j.nurick.dial.pipex.com/Code/index.htm

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

http://support.microsoft.com/?id=210111

 

[Dirk Goldgar]

Hi Dirk and Jeff


Unless you've declared
Const acImportFixed = 1
Const acQuitSaveNone = 2
this won't do what it says. In particular, it's probably doing a
delimited import not a fixed-width one. (Another lesson to ALWAYS
declare Option Explicit).

Argh! That's *got* to be it!

Testing ... Yes, when I use the numeric values of the constants, it
works fine.

Good eyes, John.

 

[Guest]

And in my haste to get this to work I left out the constants too, even though
Bob originally told me to use them. My understanding of scripting is very
little, this is the first thing I have tried, but I wanted to use it to put
together a tool that others can use without alot of extra help.

Thanks to both of you. I appreciate it alot.

 

[Guest]

By the way, how do I declare option explicit and what does it do? Last
thing, can either of you point me to a good site for scripting beginniners
like me?

 

[Douglas J Steele]

By the way, how do I declare option explicit and what does it do?

Simply put the two words:

Option Explicit

as the first line of your script.

This will mean that the script will fail if there are any variables in your
script that haven't been declared. Be aware that it won't guarantee that
you've assigned values to the variables you've declared. However, in this
case, the fact that the script would have failed because of the references
to acImportFixed and acQuitSaveNone should be enough to remind you that you
need to define them.

Last thing, can either of you point me to a good site for scripting beginniners
like me?

You could start by looking at what Alex Angelopoulos has at
http://www.mvps.org/scripting/ and look at his links. Other than that, hang
out in the Scripting newsgroups and see what resources get mentioned there
repeatedly, or do a search on VBScript using your favourite web search
engine.

 

[Guest]

Doug, thanks for the advice, I will work on this stuff, it is really cool
hitting an icon on the desktop and seeing all this data analysis get done.