close

Privacy guaranteed - Your email is not shared with anyone.

Welcome to Glock Talk

Why should YOU join our Glock forum?

  • Converse with other Glock Enthusiasts
  • Learn about the latest hunting products
  • Becoming a member is FREE and EASY

If you consider yourself a beginner or an avid shooter, the Glock Talk community is your place to discuss self defense, concealed carry, reloading, target shooting, and all things Glock.

vbs coder

Discussion in 'Tech Talk' started by gudel, Jun 18, 2004.

  1. gudel

    gudel

    486
    0
    Jun 1, 2001
    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.
     
  2. Blazen

    Blazen B-Lazy

    42
    0
    Oct 3, 2001
    Texas

  3. Blazen

    Blazen B-Lazy

    42
    0
    Oct 3, 2001
    Texas
    Also from my understading...ASP and VBscript play hand and hand...so things should work the same; might be a slight change in coding.
     
  4. What environment are you using? Word macros, compiled VB, MS Access application or ASPs?
     
  5. gudel

    gudel

    486
    0
    Jun 1, 2001
    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.
     
  6. That shouldn't be too hard. I have lots of experience with the ADO code, so let me know if you need a hand.
     
  7. gudel

    gudel

    486
    0
    Jun 1, 2001
    okay, i have an MS Access .mdb file. I need to read/write to the record/field.

    I got this part

    Code:
    
    [color=orange]'* i make connection here[/COLOR] 
    SET CONN = CreateObject("ADODB.Connection")
    
    [color=orange]'* this i don't understand, what do i do here to link to a straight .mdb file[/COLOR] 
    Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & "c:\filename.mdb"
    
    [color=orange]'* i think i'm gonna need this for write[/COLOR] 
    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.
     
  8. gudel

    gudel

    486
    0
    Jun 1, 2001
  9. 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
     
  10. gudel

    gudel

    486
    0
    Jun 1, 2001
    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
     
  11. When you can query the recordset from my hand, grasshopper... ;)
     
  12. gudel

    gudel

    486
    0
    Jun 1, 2001
    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?
     
  13. 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"), "&&&-&&&-&&&&")
     
  14. gudel

    gudel

    486
    0
    Jun 1, 2001
    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.
     
  15. 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. :)