Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    Unanswered: Please Help Just Upsized Access To Sql And Now Code Doesnt Work

    here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

    Public Function chartlookup()
    Dim db As DAO.Database <----believe to be the problem
    Dim rs As DAO.Recordset <----- belive to be the problem
    Dim SQL As String
    Dim NewNum As Integer
    Dim NeWChartNum As String

    SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

    Set db = CurrentDb() <----- -problem
    Set rs = db.OpenRecordset(SQL) <----problem

    If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400


    If rs.EOF = False Then
    If IsNull(rs!RecNum) = False Then
    NewNum = rs!RecNum + 1
    Else
    NewNum = 1
    End If
    End If
    'If NewNum = 1 Then

    [Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
    'End If

    400 End Function

    basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What error are you receiving?

    Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

    You have a few access specific casts there that are going to cause trouble for you.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Change you dao to ado - using recordset and/or connection objects.

  4. #4
    Join Date
    Sep 2003
    Posts
    41
    Originally posted by Teddy
    What error are you receiving?

    Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

    You have a few access specific casts there that are going to cause trouble for you.
    im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.

  6. #6
    Join Date
    Dec 2003
    Posts
    4

    Re: Please Help Just Upsized Access To Sql And Now Code Doesnt Work

    Hey
    Who one saying that ODBC Connections not possible by DAO?


    [QUOTE][SIZE=1]Originally posted by opcbriley
    here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

    Public Function chartlookup()
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim rs As DAO.Recordset <----- belive to be the problem
    Dim SQL As String
    Dim NewNum As Integer
    Dim NeWChartNum As String

    SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

    Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
    ............

    You may also open connection by using this code
    remaining code will remain same
    plz use it n reply me
    thx

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.

  8. #8
    Join Date
    Dec 2003
    Posts
    4

    Smile

    yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library

  9. #9
    Join Date
    Sep 2003
    Posts
    41

    thankx

    i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.


    now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

Posting Permissions

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