Results 1 to 7 of 7

Thread: VBA to SQL

  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: VBA to SQL

    Can this be done in an SQL query?

    Code:
    Private Sub Command111_Click()
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[ListAgentOfficeKey] = [TblCARETSOfcRostersAPR10].[OFFICEKEY] Set [tblCARETSData].[ListOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[SaleAgentOfficeKey] = [TblCARETSOfcRostersAPR10].[OFFICEKEY] Set [tblCARETSData].[SaleOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED NORMALIZING tblCARETSData OFFICE NAMES "
    . . . and if so . . . how?

    Rick

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[ListAgentOfficeKey] = [TblCARETSOfcRostersAPR10].[OFFICEKEY] Set [tblCARETSData].[ListOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]" 'this is a query , written in SQL
    CurrentDb.Execute strSQL 'this is that SQL being EXECUTED
    strSQL = "UPDATE [tblCARETSData] INNER JOIN [TblCARETSOfcRostersAPR10] ON [tblCARETSData].[SaleAgentOfficeKey] = [TblCARETSOfcRostersAPR10].[OFFICEKEY] Set [tblCARETSData].[SaleOfficeName] = [tblCARETSOfcRostersAPR10].[Officename]"'this is a query , written in SQL
    CurrentDb.Execute strSQL 'this is that SQL being EXECUTED
    MsgBox "FINISHED NORMALIZING tblCARETSData OFFICE NAMES "
    ...so yes, save that SQL as a query and run it
    ..the only problem that you can hit is when you have used, say variables / controls or values from the current form or report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Actually I only needed one of these as I'm only updating city names by zip code in the CrmlsOfficeRoster table.

    Code:
    strSQL = "UPDATE [CRMLSOfficeRoster] INNER JOIN [TblCAZIPCODES] ON [CRMLSOfficeRoster].[PostalCode] = [TblCAZIPCODES].[ZipCode] Set [CRMLSOfficeRoster].[City] = [tblCAZIPCODES].[City]" CurrentDb.Execute strSQL 
    MsgBox "FINISHED NORMALIZING CRMLSOfficeRoster CITY NAMES"
    An error occurs stating "An invalid statement; expected UPDATE . . . "

    It looks good to me?

    Rick

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Did your code from post #1 run?
    personally id expect

    update mytable set acolumn = t2.bcolumn inner join.......

    but for the lufe if me I cannit understand why you are uodating the city based on a zipcode as the ibformation us static, derived and therefore duplicated... goes against the principles behibd relational databases for seemingly no good reason
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Quote Originally Posted by healdem View Post
    Did your code from post #1 r
    un?
    personally id expect

    update mytable set acolumn = t2.bcolumn inner join.......

    but for the lufe if me I cannit understand why you are uodating the city based on a zipcode as the ibformation us static, derived and therefore duplicated... goes against the principles behibd relational databases for seemingly no good reason
    What I have is two tables.

    One has all CA zip codes but no city names in the City field.

    The other table, CAZIPCODES has the zip codes with their city names in a separate field.

    I wish to query to got the City names matching zip code fields of the other table.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your update query is:-
    Code:
    UPDATE [CRMLSOfficeRoster] INNER JOIN [TblCAZIPCODES] ON [CRMLSOfficeRoster].[PostalCode] = [TblCAZIPCODES].[ZipCode] Set [CRMLSOfficeRoster].[City] = [tblCAZIPCODES].[City]
    ....save that in a query
    run that query
    if it doesn't run then what error message does it report

    if it were me I'd just join tot he zip code table as and when required
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    That did it perfectly.

    Thanks VERY MUCH healdem!

    Rick

Posting Permissions

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