VBScript /SQL

[pgriffin]

I have a project in which I need to read data from an Access table via an ODBC connection and send the data to an applicaton. The issue I encountered is that I would not know the ODBC name (DSN) or the table name OR the field names.

So I came up with this code to read the fields by ordinal instead of by name. You may find it useful. I am SURE it can be improved upon, but this example shows defining a few variables in MacroScheduler, then going to VBScript to send the SQL calls to Access.

In my case, Access assigns an index number to each row, which I exclude by getting the field count, the subtracting one.

Obviously, you will need to change the var names to suit your own usage.

comments/additions are welcome.

Code: Select all

VBSTART

Dim SQLString
dim pDataDB
dim pData

Function OpenProcessData(ODBCname,TableName)
    OpenProcessData = 0
    Set pDataDB = CreateObject("ADODB.Connection")
	on error resume next
    pDataDB.Open "DSN=" & ODBCname
	if err then
	   OpenProcessData = 1
	else
       SQLString = "Select * from " & TableName
       set pData = pDataDB.Execute(SQLString)
	   if err then
	      OpenProcessData = 2
	   end if
       on error resume next
       pData.MoveFirst
	 end if
End Function

Function GetFieldCount
   GetFieldCount = pData.Fields.Count
End Function

Function GetRec(mx)
   dim i
   If Not pData.EOF then
      For i = 1 to mx
         GetRec = GetRec & pData.Fields(i) & ";"
	  Next
   pData.MoveNext
   else
      GetRec = 0
   end if
End Function

Sub CloseProcessData
    ProcessDataDB.Close
End Sub

VBEND
let>cProc=c:\program files\mjt\macroscheduler\beta\DBLoopTest.scp
let>ODBCname=ProcessData
let>TableName=ProcessDetails
VBEval>OpenProcessData("%ODBCname%","%TableName%"),reply
VBEval>GetFieldCount,fcount
let>fcount=fcount-1
Label>ReadData
VBEval>GetRec(%fcount%),record
if>record=0
   goto>done
endif>
len>record,lenrec
let>end=lenrec-1
midstr>record,1,%end%,record
sep>record,;,f
goto>ReadData
label>done