Access scripts in "Community Submitted Scripts" by Kent Finkle Q2

Discussion in 'Scripting' started by David Schrader, Oct 3, 2006.

  1. Kent,

    Two questions, one each addressed in a message to this NG as
    directed by the web page at the Microsoft site. Wish I'd been
    able to do this via e-mail to reduce network traffic.

    I've downloaded all of your scripts which show how to create
    and populate an "Access" database using MS scripts - some-
    thing I've been trying to do for quite some time but haven't
    been able to find anything about (until I found your pages
    at the MS site). I've tried them all and they all work just
    and written and even with a bit of fiddling to allow for the
    change of file name(s) and locations, table names and columns
    (or fields) names it still all works perfect.

    Question 2:

    My second question is more difficult and covers most of your
    examples in one way or another. I note that your example of
    how to create an Access database was written as a function,
    and thank you for that, I really want to modularize everything
    as much as I can. I took all of your examples, which ran perfectly,
    and tried to convert them into "Functions" or "Subroutines." It
    was a total disaster. Where you had:

    '==== Your Code
    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Scripts\Test.mdb"

    sql = "CREATE TABLE Hardware (Device Text, Location Text)"

    objConnection.Execute Sql


    '==== End Your Code

    I tried:

    '==== My Attempt

    ' The Call
    CreateNewMDBTable "C:\MigrateHomePath.mdb", "Hardware", _
    "Device Text, Location Text"

    ' As a SUB
    Sub CreateNewMDBTable( MDBName, MDBTableName, MDBFieldName )

    Dim SQLString

    ' REM for later - Check to see there are three arguments
    ' REM for later - Check to see if the database exists

    Set objConnection = CreateObject("ADODB.Connection")

    SQLString = chr(34) & " CREATE TABLE " & MDBTableName & _
    " (" & MDBFieldName & ")" & chr(34)

    ' SQLString should now contain the string:
    ' *"CREATE TABLE Hardware (Device Text, Location)"*
    ' without the * at each end.
    objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & MDBName
    sql = SQLString
    ' At this point either should work but neither do...
    objConnection.Execute Sql
    objConnection.Execute SqlString

    End Sub

    '==== End My Attempt

    For some reason the SQL call gets mangled and can't be read
    and acted upon properly. Even though logic would suggest
    that there should be no difference between the two setups.
    I've tried a series of alternative ways of writing it and it seems
    nothing other than your's works.

    I've found that I have been unable to convert any of your
    examples to either Functions or Subroutines. I can only guess
    that it has something to do with the way the scripting, SQL, and
    Access interact with one another. Do you have any suggestions
    or tips that I might use to modularize your examples into
    either Subroutines or Functions so I can "Call" them "as/when
    I need them" during the flow of another larger script?

    Many thanks for any assistance you might be able to provide
    and for all of the assistance you've already given via the
    scripts you've posted.

    David Schrader
    Florida State University
    David Schrader, Oct 3, 2006
    1. Advertisements

  2. David Schrader

    Marco Shaw Guest

    See below.

    (PS Using a real/valid email address in a newsgroup may cause a lot of
    One thing to help in troubleshooting is just before executing code, have it
    output the string
    it is going to try to run.

    A simple 'wscript.echo' just before this section, may help:

    wscript.echo sqlstring
    objConnection.Execute Sql
    objConnection.Execute SqlString

    Marco Shaw, Oct 6, 2006
    1. Advertisements

  3. Again, thanks for the feedback but this time no
    help. I've already tried this with no success at
    all. As far as I can tell everything *looks* fine
    and *looks* exactly like the line looks when it
    is typed in as if it were (more-or-less) a string

    I'm not giving up, just on a siding taking on a
    load of other issues that I'll iron out and then
    I'll get back to this specific one.

    Again, thanks. (Maybe Kent will provide some
    feedback about his stuff?)

    David Schrader, Oct 10, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.