Get Variable From Access Table Record

I need an example of a vbscript that connects to an Access table and
returns a string variable based on a WHERE clause. For example, I have
a autonumber field in numerical sequence, and I want to return a name
for each record.

I also need a script that will query a table and return a total count
of all records.


Dim strDatabase
Dim strTableName
Dim recordCount
Dim strName

strDatabase = "\\xxxxxxx\db.mdb"
strTableName = "tblAccounts"

'ACCESS CONNECTION STRING VARIABLE
xConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase


strName =

Kai
Tue Nov 27 16:57:03 PST 2007

set conn=WScript.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\server\db.mdb"

set rs=WScript.CreateObject("ADODB.recordset")

SQL = "SELECT * FROM [table] WHERE [your condition]"
rs.Open sql, conn

if NOT rs.EOF then
MsgBox rs("[field name]")
End If

rs.close
conn.close

For return a total count, you may use
SELECT COUNT("[field name]") as total_count FROM [table]
rs("[total_count]")

Hope it may help.
Best Regards,
Kai

On Nov 28, 8:09 am, tshil...@comcast.net wrote:
> I need an example of a vbscript that connects to an Access table and
> returns a string variable based on a WHERE clause. For example, I have
> a autonumber field in numerical sequence, and I want to return a name
> for each record.
>
> I also need a script that will query a table and return a total count
> of all records.
>
> Dim strDatabase
> Dim strTableName
> Dim recordCount
> Dim strName
>
> strDatabase = "\\xxxxxxx\db.mdb"
> strTableName = "tblAccounts"
>
> 'ACCESS CONNECTION STRING VARIABLE
> xConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
>
> strName =

McKirahan
Tue Nov 27 17:06:32 PST 2007

<tshillam@comcast.net> wrote in message
news:bd2183c3-851d-4a20-bd0f-7c8d3a4fccb7@e23g2000prf.googlegroups.com...
> I need an example of a vbscript that connects to an Access table and
> returns a string variable based on a WHERE clause. For example, I have
> a autonumber field in numerical sequence, and I want to return a name
> for each record.
>
> I also need a script that will query a table and return a total count
> of all records.
>
>
> Dim strDatabase
> Dim strTableName
> Dim recordCount
> Dim strName
>
> strDatabase = "\\xxxxxxx\db.mdb"
> strTableName = "tblAccounts"
>
> 'ACCESS CONNECTION STRING VARIABLE
> xConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase
>
> strName =

Will these function help? There both inside one VBScript file.


Option Explicit
WScript.Echo "Function FieldValue() :" & vbTab & FieldValue()
WScript.Echo "Function RecordCount() :" & vbTab & RecordCount()

Function FieldValue()
FieldValue = "?"
'*
'* Declare Constants
'*
Const strDatabase = "yourDatabase.mdb"
Const strTableName = "tblAccounts"
Const strFieldName = "yourFieldName"
Const strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
'* Declare Variables
'*
Dim strSQL
strSQL = "SELECT " & strFieldName _
& " FROM " & strTableName _
& " WHERE Autonumber = 1"
Dim strFieldValue
'*
'* Declare Objects
'*
Dim objADO
Set objADO = CreateObject("ADODB.Connection")
objADO.Open strConnection & strDatabase
Dim objRST
Set objRST = objADO.Execute(strSQL)
'*
'* Read Recordset
'*
If Not objRST.EOF Then
strFieldValue = objRST(strFieldName).Value
End If
'*
'* Destroy Objects
'*
Set objRST = Nothing
Set objADO = Nothing
'*
'* Function Return
'*
FieldValue = strDatabase & vbCrLf & strSQL & vbCrLf & strFieldValue
End Function

Function RecordCount()
RecordCount = -1
'*
'* Declare Constants
'*
Const strDatabase = "yourDatabase.mdb"
Const strTableName = "tblAccounts"
Const strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
'*
'* Declare Variables
'*
Dim strSQL
strSQL = "SELECT COUNT(*) As theRecordCount" _
& " FROM " & strTableName
Dim intRecordCount
'*
'* Declare Objects
'*
Dim objADO
Set objADO = CreateObject("ADODB.Connection")
objADO.Open strConnection & strDatabase
Dim objRST
Set objRST = CreateObject("ADODB.Recordset")
objRST.Open strSQL,objADO,1,1
'*
'* Count Recordset
'*
If Not objRST.EOF Then
intRecordCount = objRST("theRecordCount").Value
End If
'*
'* Destroy Objects
'*
Set objRST = Nothing
Set objADO = Nothing
'*
'* Function Return
'*
RecordCount = strDatabase & vbCrLf & strSQL & vbCrLf _
& intRecordCount & " records"
End Function