Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: Basic VB, moving data/getting records

    Hey guys

    This is infuriating. I want to be able to do the most simple things, and yet the Access help is about as helpful as something I can't talk about somewhere minors may be exposed.

    I'm from a Lotus Notes background, in case that helps anybody, but basically, I just want to get a handle on a record based on an ID. I'm using an Access Data Project linked to an MS SQL server, and I have managed (after many hours) to get a list of dates to display in a listbox, and when the user selects a date I can get hold of the ID of the entity I need.

    But how can I actually get hold of an entity, so that I can do things with it. In LotusScript, this is:

    Dim ws as New NotesUIWorkspace
    Dim uidoc as NotesUIDocument
    Set uidoc = ws.currentDocument

    Then you have uidoc, which is a NotesDocument (record/entity). You can then retrieve data from it:

    ...
    uidoc.fieldSetText(field1, "Value of Field 1")

    This changes the value of field 1 to "Vaue of Field 1". I was told that VBScript and LotusScript were essentially the same, but I can't find how to do this sort of thing. I have an entity ID, and I want to retrieve data from that entities fields, how?!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Fishkake - how's that learning curve going?

    I do use Access help but Google is my first port of call usually.

    Anyway - you have two main options - ADO and DAO. ADO would be my choice in your situation. I am not too familiar with adps however I believe there is an ActiveConnection property\ method that returns an ADO connection object connected to your SQL Server BE. Use this as the ActiveConnection of your recordset object. Of course, there might be a simpler adp way of getting at your data....
    See how you get on and post if you struggle to get the goods.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    28
    Hello again! Whenever I peruse forums, I always seem to find a single person who helps me. There's a guy called Pascal on Tek-Tips who practically wrote my Lotus Notes project for me, to whom I am eternally grateful.

    Its not you, is it? ;-)

    I found this in the help:

    Sub ConnectionObjectX()

    Dim wrkJet as Workspace
    Dim dbsNorthwind As Database
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim conPubs2 As Connection
    Dim conLoop As Connection
    Dim prpLoop As Property

    ' Open Microsoft Jet Database object.
    Set wrkJet = CreateWorkspace("NewJetWorkspace", _
    "admin", "", dbUseJet)
    Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

    ' Create ODBCDirect Workspace object and open Connection
    ' objects.
    Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
    "admin", "", dbUseODBC)

    ' Note: The DSNs referenced below must be configured to
    ' use Microsoft Windows NT Authentication Mode to
    ' authorize user access to the Microsoft SQL Server.
    Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
    "ODBC;DATABASE=pubs;DSN=Publishers")

    Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
    True, "ODBC;DATABASE=pubs;DSN=Publishers")

    Debug.Print "Database properties:"

    With dbsNorthwind
    ' Enumerate Properties collection of Database object.
    For Each prpLoop In .Properties
    On Error Resume Next
    Debug.Print " " & prpLoop.Name & " = " & _
    prpLoop.Value
    On Error GoTo 0
    Next prpLoop
    End With

    ' Enumerate the Connections collection.
    For Each conLoop In wrkODBC.Connections
    Debug.Print "Connection properties for " & _
    conLoop.Name & ":"

    With conLoop
    ' Print property values by explicitly calling each
    ' Property object; the Connection object does not
    ' support a Properties collection.
    Debug.Print " Connect = " & .Connect
    ' Property actually returns a Database object.
    Debug.Print " Database[.Name] = " & _
    .Database.Name
    Debug.Print " Name = " & .Name
    Debug.Print " QueryTimeout = " & .QueryTimeout
    Debug.Print " RecordsAffected = " & _
    .RecordsAffected
    Debug.Print " StillExecuting = " & _
    .StillExecuting
    Debug.Print " Transactions = " & .Transactions
    Debug.Print " Updatable = " & .Updatable
    End With

    Next conLoop

    dbsNorthwind.Close
    conPubs.Close
    conPubs2.Close
    wrkJet.Close
    wrkODBC.Close

    End Sub


    Is this the sort of thing I want? I think I can just about figure this out, with a healthy dose of caffeine...

    BTW - how do I do code tags? Is it the square brackets?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fishkake
    Hello again! Whenever I peruse forums, I always seem to find a single person who helps me. There's a guy called Pascal on Tek-Tips who practically wrote my Lotus Notes project for me, to whom I am eternally grateful.

    Its not you, is it? ;-)
    Lol - nope - I won't be writing it because I fear that you could be on a fools errand here - it is rather ambitious especially for someone learning vb, SQL, access and SQL Server. I more than happy to help where I can though

    Ok - you have found some DAO code. Have a look at the below ADO code. A better proposition (IMHO) for interacting with SQL Server. DAO is no longer supported by MS (although it is mature) whereas ADO is.

    Code:
    Dim rst1 As New ADODB.Recordset
     
    	rst1.Open "Select * from FooBar Where Foo = '" & Me.Bar & "'"", Application.CurrentProject.Connection, adOpenKeyset
     
    	Do While Not rst1.EOF
     
    		Debug.Print rst1.Fields(0).Value
    		rst1.MoveNext
     
    	Loop
     
    	rst1.Close
    	Set rst1 = Nothing
    This is fairly simple - as you get more advanced you can start using the connection, command, parameter and field objects. You can do lots with variations of the above though.

    HTH
    Last edited by pootle flump; 12-08-05 at 10:45.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - the [ CODE] [ /CODE] tags (without the spaces) can be easily inserted. Press the Hash (or pound if you are over the pond) sign above - #
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This link gets passed around here quite a bit...

    If you're coming out of a notes environment, I would HIGHLY recommend giving it a read. Notes is like the anti-relational database. It's a strange transition to go between the two without a solid primer...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2005
    Posts
    28
    Ugh... This looks like the sort of document I was force fed at Uni!

    With a bit of tweaking and wrestling with helpfiles, I think I now understand the ADO code you've put up there. The thing is, I know a tiny bit of SQL, and I knkow LotusScript like the back of my hand, so I know once I get going I'll be OK. Its just the starting out that's a problem!

    Thanks a bunch dude, you've helped me skip a LOT of reading time!! I'm sure I'll be back...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by fishkake
    Ugh... This looks like the sort of document I was force fed at Uni!
    Except this time you will have a use for it Teddy is right - this has been posted quite a few times in the last few days. I decided to skim it again last night and had a few "oh yeah - I'd kind of forgotten that" moments. Well worth the effort because it can save you an enourmous amount of time and heartache in the future.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •