Results 1 to 11 of 11
  1. #1
    Marykay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2019
    Location
    Philadelphia, PA
    Posts
    4

    Question Convert ASP program to VBA

    Hello all, first time posting but long time lurker who has gleaned much helpful information here.



    I have been using an ASP program that was written for me many eons ago to perform actions on tables in Access (2003). I would like to get it into VBA inside Access itself, to not have any external dependencies.

    I have experience with Access, and I have worked in VBA to get things done that I want to do. However I am not a programmer by any stretch of the imagination.

    For the last few weeks I have made several attempts at reworking the thing but I haven't even been able to get any of them to compile. The errors I'm getting don't make sense at all.

    Google has not been helpful either. The only thing I've found is that it's supposed to be simple (at least for a programmer) since ASP is so close to VB (full visual basic).

    If anyone can point me towards any resources that can help me fill in the gaps in my knowledge I would be extremely grateful.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post the ASP code? Would be easier to advise you.....

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    since ASP is so close to VB (full visual basic).
    Just to clarify - VB (Visual Basic) is not VBA. It's close in a lot of ways but not exactly the same.
    Without knowing what your gaps are, hard to recommend anything.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Marykay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2019
    Location
    Philadelphia, PA
    Posts
    4
    This is the ASP code (minus the HTML). It connects via ODBC into the Access file:

    Code:
    <%Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.open "AMSEAT"
    
    
    Set RS = Server.CreateObject("ADODB.Recordset")
    
    
    Set RS2 = Server.CreateObject("ADODB.Recordset")
    Dim actnum
    Dim seats
    Dim SQL
    Dim SQL2
    Dim endmark
    Dim finish
    Dim used
    actnum = 0
    
    
    
    
    SQL = "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO; "
    
    
    RS.Open SQL, Conn,3,3
    
    
    RS.MoveLast
    RS.MoveFirst
    'RESPONSE.WRITE SQL
    
    
    
    
    Do While Not RS.EOF
    
    
    seats = CInt(RS("SEATS"))
    'RESPONSE.WRITE RS("DACTNO")
    
    
    
    
    SQL2 = "select * from mseat where avail >= " & seats & " order by table"
    RS2.Open SQL2, Conn,3,3
    RS2.MoveLast
    RS2.MoveFirst
    
    
    Afinish = RS2("USED")
    endmark = seats
    
    
    RS2("AVAIL") = RS2("AVAIL") - seats
    RS2("USED")= RS2("USED") + seats
    RS2.Update
        do while endmark > 0
                endmark = endmark - 1
                 Afinish = Afinish + 1
    
    
                    If Afinish = 1 Then
                        
                        RS2("SEAT1") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 2 Then
                        
                        RS2("SEAT2") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 3 Then
                        
                        RS2("SEAT3") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 4 Then
                        
                        RS2("SEAT4") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 5 Then
                        
                        RS2("SEAT5") = RS("dactno")
                        'RS2.Update
            End If
    
    
                    If Afinish = 6 Then
                        
                        RS2("SEAT6") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 7 Then
                        
                        RS2("SEAT7") = RS("dactno")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 8 Then
                        
                        RS2("SEAT8") = RS("DACTNO")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 9 Then
                        
                        RS2("SEAT9") = RS("dactno")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 10 Then
                        
                        RS2("SEAT10") = RS("dactno")
                        'RS2.Update
                    End If
    
    
                    If Afinish = 11 Then
                        
                        RS2("SEAT11") = RS("DACTNO")
                        'RS2.Update
                    End If
              RS2.Update
    loop
    
    
    RS2.Close
    RS.MoveNext
    Loop
    
    
    RS.Close
    RESPONSE.WRITE ("Seating Complete!")
    %>
    This is about my third attempt to get it into VBA inside the Access file that the ASP connects to:

    Code:
    Private Sub Seating()
    
    
    'recordset and connection variables
    Dim db As Database
    Dim objRS As Recordset
    Dim objRS2 As Recordset
    Dim strSQL As String
    Dim srtSQL2 As String
    'Dim Cnxn As ADODB.Connection
    'Dim strCnxn As String
    
    
    'connection
    'Set Cnxn = CREATEOBJECT("ADODB.Connection")
    'strCnxn = "AMSEAT"
    'Cnxn.Open strCnxn
    
    
    Set db = CurrentDb
    Set objRS = CREATEOBJECT("Recordset")
    Set objRS2 = CREATEOBJECT("Recordset")
    
    
    'other variables
    Dim Msg1 As String
    Dim actnum As Integer
    Dim seats As Integer
    Dim endmark As Integer
    Dim finish As Integer
    Dim used As Integer
    'set actnum = 0
    
    
    
    
    'strSQL = "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO;"
    
    
    objRS.Open "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO", db, adOpenDynamic, adLockPessimistic
    'objRS.Refresh
    objRS.MoveLast
    objRS.MoveFirst
    
    
    
    
    Do While Not objRS.EOF
    
    
    seats = CInt(objRS("SEATS"))
    
    
    
    
    'strSQL2 = "select * from mseat where avail >= " & seats & " order by table;"
    
    
    objRS2.Open "select * from mseat where avail >= " & seats & " order by table", db, adOpenDynamic, adLockPessimistic
    'objRS2.Refresh
    objRS2.MoveLast
    objRS2.MoveFirst
    
    
    Afinish = objRS2("USED")
    endmark = seats
    
    
    objRS2("AVAIL") = objRS2("AVAIL") - seats
    objRS2("USED") = objRS2("USED") + seats
    objRS2.Update
        Do While endmark > 0
                endmark = endmark - 1
                 Afinish = Afinish + 1
    
    
                    If Afinish = 1 Then
                        objRS2("SEAT1") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 2 Then
                        objRS2("SEAT2") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 3 Then
                        objRS2("SEAT3") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 4 Then
                        objRS2("SEAT4") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 5 Then
                        objRS2("SEAT5") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 6 Then
                        objRS2("SEAT6") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 7 Then
                        objRS2("SEAT7") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 8 Then
                        objRS2("SEAT8") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 9 Then
                        objRS2("SEAT9") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 10 Then
                        objRS2("SEAT10") = objRS("DACTNO")
                    End If
    
    
                    If Afinish = 11 Then
                        objRS2("SEAT11") = objRS("DACTNO")
                    End If
              objRS2.Update
    Loop
    
    
    objRS2.Close
    objRS.MoveNext
    Loop
    
    
    objRS.Close
    Msg1 = MsgBox("Seating Complete!", vbOKOnly)
    
    
    End Sub

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not sure I understand.....(yet)

    You have an Access FE (A2010) you want to connect to an Access BE (A2003)?
    The FE would/should be on your local computer. Where is the BE located? Hopefully on the same LAN.


    It looks like you are using a mixture of ADO and DAO in the your VBA code.
    Have you set up an ODBC connection on your computer to the Access BE (A2003)?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Aside from saying you have knowledge gaps, there's been no info about what the purpose of the code is or what the error messages are.
    Since you are trying to move the operation into Access, it's possible that the whole connection part isn't required - assuming the tables you're attempting to modify are in the same db as the code. Also possible that a recordset isn't needed. Maybe explain what the task is?

  7. #7
    Marykay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2019
    Location
    Philadelphia, PA
    Posts
    4
    The ASP program performs the actions on the tables in Access 2003. If it’s moved inside Access I no longer need the connection.

    In trying to understand what parts I needed and what parts I didn’t I was commenting out rather than removing anything.

    This is a seating program for a large assigned seating gala dinner. It takes attendees and places them into available seats, looping through the records until all have been placed.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can't write a complete procedure because I don't understand the calculations and exactly what they're being performed on. The gist of my suggestion would be to open one recordset and loop through it to get the field values you need, then use append or update sql (can't tell which) as follows
    Code:
    strSQL = "SELECT DACTNO, VIP, date,COUNT(DACTNO) AS SEATS From [F6-DINNER] GROUP BY VIP,date,DACTNO"
    
    SET objRS = db.OpenRecordset strSQL
    If Not (objRS.BOF AND objRS.EOF) Then
      Do While Not objRS.EOF
    '*******************************
    'get the required field values from the opened recordset then run either append or update query sql as in
    db.Execute "UPDATE TableName SET TableName.field1 = rs.field("name1"), TableName.field2 = rs.field("name2")... ,dbFailOnError
    '*******************************
    
    objRS.MoveNext
    Loop
    If you write code, I suggest you find a more intuitive naming convention that you like and use it in the future. A lot of variables named like "actnum" makes it more difficult to know what type a variable is supposed to be when you're reading many lines down in the code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looking at the ASP code, I can see:

    Date is a reserved word and a built in function.
    Table is a reserved word.
    (Reserved words should not be used for object names.)


    There are at least 2 tables

    Table [F6-DINNER] (poor Name- BTW), with fields:
    ------------------------------------------------------
    DACTNO
    VIP
    date

    Table mseat, with fields:
    ------------------------------------------------------
    table
    AVAIL
    USED
    SEAT1
    SEAT2
    SEAT3
    SEAT4
    SEAT5
    SEAT6
    SEAT7
    SEAT8
    SEAT9
    SEAT10
    SEAT11
    ------------------------------------------------------

    Table "mseat" is not normalized. Cannot have tables with greater than 11 seats.


    There is a variable, "finish", declared as an Integer, but not used.
    There is a variable, "Afinish", NOT declared, but used.


    Without more info about your dB, the conversion from ASP to VBA code can be close, but probably won't work correctly.



    I asked questions that you didn't answer:
    Q1) You have an Access FE (A2010) you want to connect to an Access BE (A2003)?
    Q2) The FE would/should be on your local computer. Where is the BE located? Hopefully on the same LAN.


    It looks like you are using a mixture of ADO and DAO in the your VBA code.
    Q3) Have you set up an ODBC connection on your computer to the Access BE (A2003)?

    Q4) Or do you want to put the code in the A2003 dB?


    All you really have is two loops. But without knowing what the table structures are, what the data looks like and what the output/results should look like, it is extremely hard to give a valid answer or even test the VBA code.

  10. #10
    Marykay is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2019
    Location
    Philadelphia, PA
    Posts
    4
    Quote Originally Posted by Micron View Post
    I can't write a complete procedure because I don't understand the calculations and exactly what they're being performed on. The gist of my suggestion would be to open one recordset and loop through it to get the field values you need, then use append or update sql (can't tell which) as follows
    Thank you, this is very helpful. I will look at modifying it this way.

    Quote Originally Posted by ssanfu View Post
    Looking at the ASP code, I can see:

    Date is a reserved word and a built in function.
    Table is a reserved word.
    (Reserved words should not be used for object names.)
    The data structure was brought forward from a legacy DB2 database. Had I been aware of this I would have changed these fields. I'll have to test the impact of changing them now.

    Quote Originally Posted by ssanfu View Post
    There are at least 2 tables

    Table [F6-DINNER] (poor Name- BTW), with fields:
    ------------------------------------------------------
    DACTNO
    VIP
    date
    I'm not sure how the brackets came about in the code as the table name in Access is "F6-DINNER".

    Quote Originally Posted by ssanfu View Post
    Table mseat, with fields:
    ------------------------------------------------------
    table
    AVAIL
    USED
    SEAT1
    SEAT2
    SEAT3
    SEAT4
    SEAT5
    SEAT6
    SEAT7
    SEAT8
    SEAT9
    SEAT10
    SEAT11
    ------------------------------------------------------

    Table "mseat" is not normalized. Cannot have tables with greater than 11 seats.
    This is correct. Before the ASP program is run, MSEAT contains 100 rows. Table numbers 1-100 in the table field, USED field is 0, AVAIL field is 10 and all seats are 0. We do not want any table to contain greater than 11 seats.

    Quote Originally Posted by ssanfu View Post
    There is a variable, "finish", declared as an Integer, but not used.
    There is a variable, "Afinish", NOT declared, but used.
    Thank you I will fix that.

    Quote Originally Posted by ssanfu View Post
    I asked questions that you didn't answer:
    Q1) You have an Access FE (A2010) you want to connect to an Access BE (A2003)?
    Q2) The FE would/should be on your local computer. Where is the BE located? Hopefully on the same LAN.

    It looks like you are using a mixture of ADO and DAO in the your VBA code.
    Q3) Have you set up an ODBC connection on your computer to the Access BE (A2003)?

    Q4) Or do you want to put the code in the A2003 dB?
    Everything is on my local machine now - ASP running in IIS on localhost, ODBC connection to A2003 database in the inetpub folder.

    The mixture of ADO/DAO was me trying to figure out how to have all the right objects without the connection objects.

    The goal is to have the code inside the A2003 database where the tables are.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are assigning names to tables/seats?
    If Yes, where do the names come from?

    The inetpub folder is on your computer?

    Are you wanting to add the code to the A2003 dB or to an Access dB (FE) on your computer?

    What would be the possibility of you posting a copy of the A2003 BE for testing?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Regarding Program
    By Kundan in forum Programming
    Replies: 2
    Last Post: 08-06-2018, 10:12 PM
  2. Regarding Program
    By Kundan in forum Programming
    Replies: 1
    Last Post: 07-31-2018, 01:05 AM
  3. Regarding program
    By Kundan in forum Programming
    Replies: 7
    Last Post: 06-12-2018, 02:12 AM
  4. Replies: 5
    Last Post: 04-09-2018, 10:30 PM
  5. Program a 30-day trial into my Access Program?
    By genghiscomm in forum Programming
    Replies: 1
    Last Post: 05-26-2011, 02:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums