[gudel]

are there any vbscript guys here? i need some pointers on how to read/write access mdb/SQL stuff.
i searched google, but mostly it's for ASP.

[Blazen]

This may or may not help you out...

http://cwashington.netreach.net/depo...tType=vbscript

Its a nice site that has loades of "scripting" examples/solutions...

;c

[Blazen]

Also from my understading...ASP and VBscript play hand and hand...so things should work the same; might be a slight change in coding.

[Darkmage]

What environment are you using? Word macros, compiled VB, MS Access application or ASPs?

[gudel]

simply vbs script from xp to read/write Access' mdb.
i found a sample code for ado, will have to try it to see if it works.

[Darkmage]

That shouldn't be too hard. I have lots of experience with the ADO code, so let me know if you need a hand.

[gudel]

okay, i have an MS Access .mdb file. I need to read/write to the record/field.

I got this part

'* i make connection here 
SET CONN = CreateObject("ADODB.Connection")

'* this i don't understand, what do i do here to link to a straight .mdb file 
Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "c:\filename.mdb"

'* i think i'm gonna need this for write 
Set RS = CreateObject("ADODB.Recordset")

I was wondering if anyone has something like this, basically it sets up the necessary code to open/read/write an .mdb Access file. Nothing fancy like opening up a query/relational or moving records etc, just the basic open/read/write/close.

[gudel]

looks like i'm on to something here, found this page
http://msdn.microsoft.com/library/de...invbscript.asp

i guess i'll just have to try them out!

[Darkmage]

It looks like you're well on your way. However, the line that starts with "OPEN" isn't going to work. You didn't specify what is going to attempt to open. It should probably read:
sSQLCONN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\filename.mdb"

In the abstract, what you are doing in the first line is defining an ADO connection object. The recordset you create in the third line will be your interface to the actual data and the recordset will use the connection object when it wonders where to go fetch.

The second line with all the gobbledygook is where you tell the connection object to establish a connection with your MS Access file. When you're done executing commands against your database, remember to close your connection object like so:
CONN.close

Now that you have an open connection and a recordset, you should now open the recordset. In order to do that you have to 1) give it a SQL query to work with and 2) tell it to use the CONN object.
RS.Open "SELECT * FROM Table1", CONN

Your code to read from "Table1" goes here.

And at last, clean up a bit.

RS.close
SET RS = Nothing
CONN.close
SET CONN = Nothing

[gudel]

yeah that Open looks weird, forgot to put the object there

RS.Open "SELECT * FROM Table1", CONN
This is the part that I often confused on the Table1 label, that line looks straight forward.

hopefully my access kungfu will be better after this ;f

[Darkmage]

When you can query the recordset from my hand, grasshopper...

[gudel]

Darkmage, perhaps you can figure this out.
I'm trying to retrieve a value from a field. it's a phone number. in access table, it shows as 800-555-1212. but when I use vbs to grab
it, all i got is 8005551212
say I put it like:
WScript.Echo objRecordset.Fields.Item("Telephone")
I could separate these using Left,Mid and Right, but that's more code Not sure if it's possible to format it, or am I just stuck with it?

[Darkmage]

The data is actually stored as a string value, as "8005551212". Access is formatting that number to make it appear like a regular phone number.

There is a Format function in VBA, but you need to use a user-defined format. There is no "phone number" format.

Try this:

sMyString = Format(objRecordset.Fields.Item("Telephone"), "@@@-@@@-@@@@")

or

sMyString = Format(objRecordset.Fields.Item("Telephone"), "&&&-&&&-&&&&")

[gudel]

it didn't work, it would've work if it's run in office apps, but i run it from script. there's no format function in in wsh/vbs for this particular kind. but that's okay, the alternative seems to work though.

[Darkmage]

Whoops! I forgot you were building an ASP. I've been fighting VB code for the past two weeks and I've got it on the brain.

I guess it will go like this then:

Dim sPhoneString
sPhoneString = objRecordset.Fields.Item("Telephone")
sPhoneString = mid(sPhoneString, 1, 3) & "-" & mid(sPhoneString, 4, 6) & "-" & mid(sPhoneString, 7, 10)

Just a little unnecessary hint: instead of trying to get everything done in one line (multiple mid statements on the full objRecordset.Fields("Telephone") thing), the above code only makes one call of the recordset object, handling the rest in VBScript code. This will save you two round-trips to the recordset object to get the value.

Granted, this is only a concern in large queries and high-traffic web sites. But it's the thought that counts.