Results 1 to 5 of 5

Thread: HELP - Access, ADODC, SQL

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    19

    HELP - Access, ADODC, SQL

    I need urgent help.

    I'll try to break it all down in a meaningful way. I've been stumped on this for weeks now.

    I've got a form with a MSHFlexgrid showing the data from a selected recordset A from a database A.

    The data is in the format: Name, Address, 2003, 2005, 2003address

    (The 2003 & 2005 fields are filled with a "Y" or are left blank) the 2003address column is empty before processing starts.

    eg.

    J Bloggs 1 Knowhere Street Y Y
    T Smith 3 Here Road Y
    A Hugh 6 There Avenue Y

    I am only concerned with the "blank" then Y records.i.e. A hugh

    Basically, what I want is to select one of these "blank" Y record on the grid and click a command which will:


    Search a table called "2003addresses" in Database B.

    ** select a "blank" Y record from grid, the search should look for the record with the same name e.g. J Bloggs in the "2003addresses" table as the selected record in the recordset A. Upon finding this record - the recordsetA should be updated, the address found in "2003addresses" to it.

    I have been trying so hard to do this,but I keep messing it up.

    Any help will be greatly appreciated.

    ManyThanks.
    Last edited by michaelscullion1; Apr 13th, 2006 at 10:42 AM.

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    19

    Re: HELP - Access, ADODC, SQL

    I seem to be getting a few people veiwing my thread...but no help.

    Can anyone suggest a way to encourage people to help me? Have I written my problem badly?

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: HELP - Access, ADODC, SQL

    Quote Originally Posted by michaelscullion1
    Basically, what I want is to select one of these "blank" Y record on the grid
    Do you want the grid to hold all the records in the database, or only those records with a blank Y field?

    and click a command which will:

    Search a table called "2003addresses" in Database B.

    ** select a "blank" Y record from grid, the search should look for the record with the same name e.g. J Bloggs in the "2003addresses" table as the selected record in the recordset A. Upon finding this record - the recordsetA should be updated, the address found in "2003addresses" to it.
    That's a select query to find the record in the 2003addresses table and an update query to update the main table.

    strsql = "Select [2003address] from 2003addresses where [name] = '" & <the name from the selected row in the grid> & "'"

    get this into a recordset, say rs2003. Then

    strSQL = "Update <maintablename> set [2003address] = '" & rs2003.Fields("2003address").Value & "' where [name] = '" & <name from the selected row in the datagrid> & "'"

    You may have a problem using 2003address as a field name and using Name as a field name is a bad choice, because "name" is a reserved word.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    19

    Re: HELP - Access, ADODC, SQL

    MY CODE

    FORM 1

    Option Explicit
    Const bigT = "ElectorRegister2005"
    Const bigT3 = "2003_COMBINED_AREAS"
    Const databaseName = "Database"
    Const databaseNameMoved = "Moved"
    Dim databaseNamePath As String
    Dim databaseNameMovedPath As String
    Dim dName() As String
    Public tmpTName As String
    Public tmpTName2 As String

    ****** LOTS OF FUNCTIONS HERE **************************
    *Basically, after processing occurs on this form the outcome is:******
    *** that "database" contains bigT3 as one of its tables************
    ***This table has fields ForeName, Surname, houseNum, Road*******

    *****the "Moved" database contains several tables, all of which are ****displayed on a list************************************
    I select a table name from the list and click a command to open up form2

    ****** ALL THIS WORKS FINE ********************************


    FORM 2

    Public Sub Form_Load()


    databaseNameMovedPath = App.Path & "\Moved.mdb"

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseNameMovedPath & ";Persist Security Info=False"
    cn.CursorLocation = adUseClient

    databaseNamePath = App.Path & "\Database.mdb"

    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & databaseNamePath & ";Persist Security Info=False"
    cn2.CursorLocation = adUseClient

    Dim cmdCreate As ADODB.Command
    Dim strCnn As String
    Dim strQry As String

    Set cmdCreate = New ADODB.Command
    Set cmdCreate.ActiveConnection = cn
    strQry = "ALTER TABLE " & frmMain.List3.Text & " ADD House2 varchar(255), RoadName2 varchar(255)
    cmdCreate.CommandText = strQry
    cmdCreate.Execute


    Set rs = cn.Execute("select * from " & frmOne.List.Text)
    Set MSHFlexGrid1.Recordset = rs

    cn.Close
    cn2.Close



    ************* ALL THIS IS FINE ****************************
    *******************************************************
    the record set shown in this grid is from the table selected from formOne..
    lets call it e.g.London****************************************
    this table contains the following fields************************
    ****ForeName, Surname, houseNum, Road, 2003, 2005, housenum2,road2*******
    the 2003, 2005 columns are either blank, or contain a "Y"********



    What is want to do now....

    is to create a command....which will :

    go through each record in the "London" table (the current record set) which has a blank 2003 field, and a "Y" in the 2005 field.

    e.g.
    Fname - Sname - HNum - Road - 2003 - 2005 - Hnum2 - Road2
    Josephine - Bloggs - 65453 - TheRoad - #blank# - "Y" - #blank# - #blank#

    now search through the table "2003combined areas" in the other database...with criteria forename and suname...

    if a matching record is found (same fname and sname).....then add the housenum and road from "2003combined areas" to the relevant record in "london"

    previously i said do it record by record, but it'd be better to do it all at once for all the records in the record set london....i.e. with a blnk 2003, and a "Y" in 2005



    SORRY for this being all over the place

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Mar 2006
    Posts
    19

    Re: HELP - Access, ADODC, SQL

    should I reword that...

    really sorry for the mess....

    does anyone know if its doable?

Posting Permissions

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



Click Here to Expand Forum to Full Width