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

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

objConnection.Close

'==== 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
objConnection.Close

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

 

See below.

(PS Using a real/valid email address in a newsgroup may cause a lot of
SPAM!)

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
objConnection.Close


Marco

 

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
constant.

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