Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Durban South Africa
    Posts
    13

    Unanswered: How do you create a record source for an unbound form?

    I am trying to use Access as a front end to another Access 2002 database which resides elsewhere on the disk. I would like to be able to bind controls eg. a text box to this database on an unbound form, but am having no success, so I tried setting the form's record source after creating an ADO connection, but I get told that access cannot find the table or query. Here is my connection code:

    Set db = New ADODB.Connection
    With db
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source") = _
    CurrentProject.Path & "\Tripsheets_0.MDB"
    .Open
    .CursorLocation = adUseServer
    End With

    Me.RecordSource = "Downtimes"

    Could somebody please point me in the right direction? Thank you in advance.
    Disk space, the final frontier...

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    Binding ADO to an Access form requires some work and there are a lot of requirements.

    Have you considered:

    Creating an ODBC Access link to the other database?
    Using DAO to bind to the recordsource?
    Creating a SQL string to set to the form's recordsource (using the IN clause which lets you choose an external database)?

    As far as your sample, I made some modifications and did some playing around and got it working.

    If your path is correct (I noticed you are using a relative path to your current Access project - CurrentProject.Path & "\Tripsheets_0.MDB") then the following code should work:

    (I assume Downtimes was a table?)

    Option Compare Database
    Option Explicit

    'change the path and MDB to your own project:
    Private Const strDatabase As String = "C:\Windows\Desktop\DemoDat.mdb"

    Private Sub Form_Open(Cancel As Integer)

    Dim cn As ADODB.connection
    Dim rs As ADODB.recordset

    Set cn = New ADODB.connection

    With cn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source").Value = strDatabase
    .Open
    End With

    Set rs = New ADODB.recordset
    With rs
    Set .ActiveConnection = cn
    .Source = "Downtimes" 'you can also use a SQL statement here
    .LockType = adLockOptimistic
    .CursorType = adOpenDynamic
    .Open
    End With

    Set Me.recordset = rs

    Set rs = Nothing
    Set cn = Nothing

    'Note: the Access database you are referencing must have
    'a unique id/primary key to be updateable. also there was
    'an issue where in Access 2000 forms you could only use
    'an updateable ADO recordset if you used
    'MSDataShape and SQL Server OLEDB providers

    End Sub

    Private Sub Form_Unload(Cancel As Integer)

    Dim cn As ADODB.connection
    Set cn = Me.recordset.ActiveConnection
    cn.Close
    Set cn = Nothing

    End Sub
    Last edited by JoeG; 01-03-04 at 17:30.

  3. #3
    Join Date
    Dec 2003
    Location
    Durban South Africa
    Posts
    13
    Thank you very much for taking the time to help me. I'll try out what you suggested just as soon as I'm able. Thank you again.
    Disk space, the final frontier...

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    justavisita,

    for some reason my demo attachment didn't post with the message. try this out.

    Dat.mdb has the sample tblCustomers table to demonstrate how this works. you can change the path to any folder you wish:
    Private Const strDatabase As String = "C:\Dat.mdb"

    joeg
    Attached Files Attached Files
    Last edited by JoeG; 01-04-04 at 11:16.

Posting Permissions

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