Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    6

    Unanswered: Access ADODB Connection

    I am using Access as a front end to an SQL server. I am trying to connect to the SQL Server using an ADODB connection on the form load. I want to get four fields out of a table on the SQL server and fill the data into a continous form in Access. I cannot tell if I am getting the connection right becuase I cannot get any data to populate on my form. I know there is data in these fields in the table on the SQL server. Any help is MUCH Appreciated. I have copied some of the code I am working on below.
    Private Sub Form_Load()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String

    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection


    'Create SQL statement
    sql = "Select PO_Num, Part_Num, Product, Pur_Pri from Item"

    'Set Up ADO Connection to SQL Server
    With conn
    .Provider = "SQLOLEDB.1"
    .ConnectionString = "Password=password;Persist Security Info=True;User ID=dpmklh;Initial Catalog=TRACKIT45_DATA;Data Source=SQLSRV01"
    .Open
    End With

    rs.Open sql, conn

    'fill values trying to fill sub form (this part does not run error free)
    ' Me.qrySelect.SubFormControlName!Quanity = " "
    ' Forms!frmInvoice!qrySelect.Form!PartNumber.Text = rs!Part_Num
    ' Forms!frmInvoice!qrySelect.Form!Description = rs!Product
    ' Forms!frmInvoice!qrySelect.Form!UnitPrice = rs!Pur_Pri
    'fields on form
    Me.PartNumber.Text = rs!Part_Num
    Me.Description.Text = rs!Product
    Me.UnitPrice.Text = rs!Pur_Pri

    'clean up
    rs.Close
    conn.Close

    End Sub

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: Access ADODB Connection

    Originally posted by khill

    rs.Open sql, conn

    End Sub
    Hi kill, :-)

    if you fill records for a continuous form, i think you would need to set that form's recordsource property = sql (e.g. myform.recordsource=sql) setting each value to each control that way would not be the fastest one.

    you didnt say anything about what the error was, so cant go further. just to help you check if the returned recordset has any data, you can put these codes after rs.open....

    dim n as integer
    n=rs.recordcount
    msgbox n

    if n>0 then you have some data

    hope this helps
    Last edited by qha_vn; 06-18-03 at 23:55.
    qha_vn

  3. #3
    Join Date
    May 2003
    Posts
    6
    qha_vn,

    Thank you for trying to help. I put in the code you suggested and it is showing -1 which is why the error message I was getting was "Either BOF or EOF is true, or the current record has been deleted". In debug mode, when I hover the mouse over the .provider and .connection string the message "Object variable or with Block not set". The problem with the BOF or EOF being true is that I know there are records in the SQL table so I should be getting some type of record set.
    I did not want to base the sub form on just the SQL statement because then the records cannot be edited. I will try to explain what my goal is here. I need to develop an invoicing type system. I need to use Access and pull information from an SQL database based on matching criteria entered in an Access form. For example, one of the fileds on the form is Request Number, based on the request number entered, I need to get the Price, the Item Description, the Number of Items ordered from the SQL table. The Access form will have other field for the user to fill in, like who the tech. was, who requested this information, the date and the invoice number. I need to store all of the information on the Access form into an Access table (this is requested by the user because he may later need to look at the information or get an exact copy of the invoice sent out). The Fields brought in from the SQL table (the price, quanity etc) need to be saved in the Access table (I am only setting up one table to store this info in). The user also needs to be able to edit the fields populated by the SQL data because the price may increase or decrease or whatever.

  4. #4
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    Originally posted by khill
    qha_vn,

    1- I did not want to base the sub form on just the SQL statement because then the records cannot be edited.

    2- ... I need to use Access and pull information from an SQL database based on matching criteria entered in an Access form. For example, one of the fileds on the form is Request Number, based on the request number entered, I need to get the Price, the Item Description, the data because the price may increase or decrease or whatever.
    Hi Khill,

    1- why do you say that, the records will be editable

    2- in your case, i think i would use an access db with some local tables for local user, and some linked tables to SQL server for the price, item description... further more the sql statement may work great with the request number known e.g. SELECT * FROM sql_server_table WHERE request_number=1234"

    hope this helps
    qha_vn

  5. #5
    Join Date
    May 2003
    Location
    sidcup kent
    Posts
    7
    Originally posted by khill
    qha_vn,

    Thank you for trying to help. I put in the code you suggested and it is showing -1 which is why the error message I was getting was "Either BOF or EOF is true, or the current record has been deleted". In debug mode, when I hover the mouse over the .provider and .connection string the message "Object variable or with Block not set". The problem with the BOF or EOF being true is that I know there are records in the SQL table so I should be getting some type of record set.
    I did not want to base the sub form on just the SQL statement because then the records cannot be edited. I will try to explain what my goal is here. I need to develop an invoicing type system. I need to use Access and pull information from an SQL database based on matching criteria entered in an Access form. For example, one of the fileds on the form is Request Number, based on the request number entered, I need to get the Price, the Item Description, the Number of Items ordered from the SQL table. The Access form will have other field for the user to fill in, like who the tech. was, who requested this information, the date and the invoice number. I need to store all of the information on the Access form into an Access table (this is requested by the user because he may later need to look at the information or get an exact copy of the invoice sent out). The Fields brought in from the SQL table (the price, quanity etc) need to be saved in the Access table (I am only setting up one table to store this info in). The user also needs to be able to edit the fields populated by the SQL data because the price may increase or decrease or whatever.


    your "block not set " error may suggest that the connection is not working

    have you checked the sql server security is correct for this user and permissions are set for this table etc

    If you are just starting this database and using access xp why not use an access project to connect directly to the database

Posting Permissions

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