Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Posts
    196

    Unanswered: ADO and Unbound forms in Access

    I have been asked to create an unbound form that links into a database via ADO. From here I need it to read fields into the text boxes and combo boxes, allow the user to make changes in the text box and write these changes back to the database.

    I’ve set up the connection in a module with the following code:

    Code:
    Option Compare Database
    Public sqlStatement As String
    Public adoCon As New ADODB.Connection
    Public recSet As New ADODB.Recordset
    Public Sub ConectedADO()
    	With adoCon
    	  .Provider = "Microsoft.Jet.OLEDB.4.0"
    	  .Open "C:\Databases\backend.mdb"
    	End With
        sqlStatement = "Select CustAccount,custName from tblCustomer"
    	With recSet
    	  Set .ActiveConnection = adoCon
    	  .CursorType = adOpenKeyset
    	  .LockType = adLockOptimistic
    	  .Open sqlStatement
    	End With
    End Sub

    Then in the form, I have the following code on the load event:

    Code:
    Private Sub Form_Load()
    Call ConnectedADO
    Set Me.Recordset = recset
    
    Me.TxtCustAccount.ControlSource = recSet.Fields.Item(0).Name
    Me.TxtCustName.ControlSource = recSet.Fields.Item(1).Name
    
    Set recSet = Nothing
    Set adoCon = Nothing
    End Sub
    When I try to add another line to populate a combo, it adds the record but does not let me select records from a drop down. Also, the text boxes are not allowing me to change the values, saying they’re read only. To write data back to the database, I presume I have to somehow stop them being read only, and create a SQL update statement that will write any changes I make back to the database which executes on a command button?
    Cheers,

    Mark

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Remember you said you wanted an unbound form - that applies to the controls too

    Write the value of the recordset fields to the controls instead.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2005
    Posts
    196
    Right - think I know what you mean! So I presume that doing this will allow them to be edited and then I can just create a button passing a SQL statement to update the database?
    Many thanks for your help.

  4. #4
    Join Date
    Aug 2005
    Posts
    196
    One other thing I forgot to ask:

    Code:
    With adoCon
    	  .Provider = "Microsoft.Jet.OLEDB.4.0"
    	  .Open "C:\Databases\backend.mdb"
    	End With
    obviously that connects to an Access database. How would I connect to a SQL Server database? I try the following line:

    Code:
     .Open "PCNAME\SQLSERVEREXPRESS\Databasename"
    but it tries to look in C\Documents & Settings\mark\My Documents\PCName\SQLServerExpress\Databasename.

    Am I going about it completely the wrong way?
    Cheers.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2005
    Posts
    196
    No matter what I do I cannot get a connection string working with a SQL Server Express 2005 database using ADO.

    I am trying the following code:


    Code:
    With adoconect
        
          .Provider = "Microsoft.Access.OLEDB.10.0"
          .Properties("Data Provider").Value = "SQLOLEDB"
          .Properties("Data Source").Value = "PC_NAME\SQLEXPRESS"
          .Properties("Initial Catalog").Value = "SQLDATABASENAME"
          .Open
       End With
    But an error message each time says invalid authorization specification. I use Windows authenticication on SQL Server. WHat am I doing wrong?

  7. #7
    Join Date
    Feb 2007
    Posts
    4
    You could set up a System ODBC DSN with the ODBC applet. Then use a connection string like this, where the DSN name is MyDSNName:

    "ODBC;DSN=MyDSNName;UID=MyUserId;PWD=MyPasswor d"

  8. #8
    Join Date
    Aug 2005
    Posts
    196
    Quote Originally Posted by GeoNet
    You could set up a System ODBC DSN with the ODBC applet. Then use a connection string like this, where the DSN name is MyDSNName:

    "ODBC;DSN=MyDSNName;UID=MyUserId;PWD=MyPasswor d"
    Man thanks for your reply. I managed to get round with the following line of code:

    Code:
    .Properties("Integrated Security").Value = "SSPI"
    This is instead of the userID and password properties

  9. #9
    Join Date
    Aug 2005
    Posts
    196
    Just one last question on this topic:

    Now instead of connecting to an external Access/SQL database, I need to connect to the local database. Obviously the folowing code does not work:

    With adoCon
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "C:\Databases\frontend.mdb"
    End With
    Frontend is the database that I am coding in - I know there is a one liner that says connect to the local database (adoconeect =localdatabase or similar?) - I just can't find it or work it out anywhere!
    Cheers.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    CurrentProject.Connection
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2005
    Posts
    196
    Brilliant - thanks for your help. Don't you just love one liners?!!

Posting Permissions

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