vbs coder [Archive] - Glock Talk

PDA

View Full Version : vbs coder


gudel
06-18-2004, 12:19
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
06-18-2004, 13:03
This may or may not help you out...

http://cwashington.netreach.net/depo/view.asp?Index=558&ScriptType=vbscript

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

;c

Blazen
06-18-2004, 13:04
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
06-21-2004, 08:33
What environment are you using? Word macros, compiled VB, MS Access application or ASPs?

gudel
06-21-2004, 10:13
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
06-25-2004, 11:03
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
07-01-2004, 14:49
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
07-01-2004, 15:12
looks like i'm on to something here, found this page
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadocodeexamplesinvbscript.asp

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

Darkmage
07-02-2004, 20:53
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
07-03-2004, 01:58
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
07-06-2004, 22:37
When you can query the recordset from my hand, grasshopper... ;)

gudel
08-31-2004, 00:33
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
08-31-2004, 08:36
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
08-31-2004, 11:31
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
09-01-2004, 12:39
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. :)