Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Unanswered: How to Link a form field to sql Db table

    Hello there,
    in the form design view how can a field be linked to a table in a database in sql server or the source control of the field on the form being a table in a database in sql server.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    The simple solution consists in creating an attached (or linked) table in the Access database and link it to a table or a view in the SQL database. You can then handle the columns of this attached table as you would for a "normal" Access table.

    A (quite) complex solution (or set of solutions) uses objects from the DAO or ADO libraries (among: WorkSpace, Connection, Database, QueryDef, RecordSet, Field for DAO; among: Connection, Command, Parameter, RecordSet, Field for ADO), and open a table or a view with a SELECT... SQL statement, or call a stored procedure that returns data, on the SQL database.

    Here's an example of a solution using DAO:
    Sub TestQueryDef()
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Set qdf = CurrentDb.CreateQueryDef
        With qdf
            .Name = ""
            .Connect = "ODBC;driver={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"
            .SQL = "SELECT Customer_P_O_Number FROM Tbl_OrderLines WHERE LCF_Data = " & Me.Text_LCF_Data
            Set rst = .OpenRecordset
            With rst
                If Not .BOF Then Me.Text_Customer_P_O_Number.Value = .Fields(0).Value
            End With
        End With
        Set rst = Nothing
        Set qdf = Nothing
    End Sub
    Have a nice day!

Posting Permissions

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