Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

URGENT : MDB-MDB (DAO) => ADP-SQL (ADO) - "Object variable or With block variable not set"

Hi.

I've been given the task of taking 2 MDB files (A frontend with forms and VBA code and a backend with only tables and queries) and upgrading it to use SQL server (SQL 2000).

I've got the database in SQL server.

Now comes the task of amending the code to work. The new file is an ADP.

I'm learning as I go about Access.

I have the following functional function (in the old version of the app) ...

Private Sub cmdNoManu_Click()
    Dim dbs As Database
    Dim rst As Recordset
   
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblTransactions", dbOpenDynaset)

    With rst
        .AddNew
        !fldTransDate = Date + Time
        !fldMouldNumber = [Forms]![frmMoulds]![fldMouldNumber]
        !fldSize = [Forms]![frmMoulds]![fldSize]
        !fldBVPatt = [Forms]![frmMoulds]![fldBVPatt]
        !fldTransactionResult = "FAILED-MANUFACTURER"
        .Update
        .Bookmark = .LastModified
    End With
   
    rst.Close
    dbs.Close
    DoCmd.Close
End Sub

But I can't get this to work in the ADP.

I'm not at all familiar with ADO or AOD, or DAO or OAD or ODA or DOA (Well, Dead On Arrival!).

I'm more familiar with SQL. I write an SQL statement, I issue it. I get the results. Simple. I'm lost at the reasoning for these additional layers. But that aside ...

I'm getting the following error ...

Run-time error '91':

Object variable or With block variable not set

for this line of code ...

Set rst = dbs.OpenRecordset("tblTransactions", dbOpenDynaset)


This code is on a form which IS communicating with the DB already. That part is working.

Ideally, I want to execute the following SQL statement ...

    s_SQL = _
        "INSERT INTO " & _
            "tblTransactions" & _
                "(" & _
                "fldTransDate, " & _
                "fldMouldNumber, " & _
                "fldBVPatt, " & _
                "fldSize, " & _
                "fldTransactionResult" & _
                ") " & _
            "VALUES" & _
                "(" & _
                "CONVERT(DATETIME, '" & Format(Date, "yyyy-mm-dd") & " " & Format("hh:mm:ss", Time) & ", 121), " & _
                [Forms]![frmMoulds]![fldMouldNumber] & ", " & _
                "'" & [Forms]![frmMoulds]![fldBVPatt] & "', " & _
                "'" & [Forms]![frmMoulds]![fldSize] & "', " & _
                "'FAILED-MANUFACTURER'" & _
                ")"

This is what I am familiar with. But I can't work out how to add this to the code.

This is the second of around 8 forms I'm on.

I'll be asking a lot of newbie style questions (each one a separate EE question).

So lots of help please.

Also, I am not particularly interested in changing the entire application to a new acronym. The reason for the conversion from MDB to SQL is that I need to add a complex realtime analysis to the app. The SQL query takes WAY too long on Access but is nearly instantaneous on SQL server. Also, the app has a shelf life as I will be re-writing this app as a touch screen web app.

All the code I'm looking for is, unfortunately, quick fix stuff.

Anyway,

Thanks for any and all help provided.

Regards,

Richard Quadling.
0
Richard Quadling
Asked:
Richard Quadling
  • 10
  • 7
1 Solution
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
I found RunSQL as a method for DoCmd, but ...

"This method only applies to Microsoft Access databases (.mdb)."

0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Oh. I realise that the parameters for the format of time is the wrong way round. Fixed.
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Found a solution ...

http://www.experts-exchange.com/Databases/MS_Access/Q_21197465.html?query=ADP&topics=39

Private Sub cmdNoManu_Click()
    Dim r_conn As ADODB.Connection
    Set r_conn = New ADODB.Connection
       
    s_SQL = _
        "INSERT INTO " & _
            "tblTransactions" & _
                "(" & _
                "fldTransDate, " & _
                "fldMouldNumber, " & _
                "fldBVPatt, " & _
                "fldSize, " & _
                "fldPrefered, " & _
                "fldAccepted, " & _
                "fldTransactionResult" & _
                ") " & _
            "VALUES" & _
                "(" & _
                "CONVERT(DATETIME, '" & Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh:mm:ss") & "', 121), " & _
                [Forms]![frmMoulds]![fldMouldNumber] & ", " & _
                "'" & [Forms]![frmMoulds]![fldBVPatt] & "', " & _
                "'" & [Forms]![frmMoulds]![fldSize] & "', " & _
                "0, " & _
                "0, " & _
                "'FAILED-MANUFACTURER'" & _
                ")"
    With r_conn
        .ConnectionString = CurrentProject.BaseConnectionString
        .Open
        .Execute s_SQL
        .Close
    End With
    Set r_conn = Nothing
End Sub
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
rockiroadsCommented:
that works just as well
to emuate your original code

Private Sub cmdNoManu_Click()

    Dim rst As ADODB.Recordset
   
    set rst = New ADODB.Recordset

    With rst
        .ActiveConnection = currentproject.connection
        .open "tblTransactions"

        .AddNew
        !fldTransDate = Date + Time
        !fldMouldNumber = [Forms]![frmMoulds]![fldMouldNumber]
        !fldSize = [Forms]![frmMoulds]![fldSize]
        !fldBVPatt = [Forms]![frmMoulds]![fldBVPatt]
        !fldTransactionResult = "FAILED-MANUFACTURER"
        .Update
    End With
   
    rst.Close
    set rst=nothing
End Sub
0
 
rockiroadsCommented:
RQuadling, I realise you had already found a solution
I was just giving you another way, that is all

all part of the learning exercise

another thing, if this code is in frmMoulds

u can just do

Me.fldMouldNumber

(etc)

instead of

[Forms]![frmMoulds]![fldMouldNumber]

(etc)

0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Oh. Mate. Sorry. You got in too late. Question PAQ'd and refunded.

But, don't worry. A nice new question on the way.
0
 
rockiroadsCommented:
ignore my code attempt
stick with insert, its better
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
THis form is frmManufacturer. The form frmMoulds has the mould number on it.

The app is a touch screen app, so big buttons, 1 input per page, etc.

Page 1 Get mould number. Accept.
Page 2 Get manufacture. Accept or no manufacturer (the bit I've just done).
Page 3 ...

I didn't get the notification for your response before I asked to close it. All very close in timings.

But, excellent that I can do this with a lot less work.

0
 
rockiroadsCommented:
RQualiding, this wasn't about the points, as I already saw you had sorted it out
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Ok.

I'd probably care more if I wasn't going to be rewriting the whole thing anyway.
0
 
rockiroadsCommented:
Ok, reason why I said the insert was I had opened the recordset incorrectly

do it this way, emulates more your original code. now this is much better and probably easier than having to create a insert statement


    rst.Open "tblTransactions", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    With rst
        .AddNew
        !fldTransDate = Now()             'Now returns current date and time
        !fldMouldNumber = [Forms]![frmMoulds]![fldMouldNumber]
        !fldSize = [Forms]![frmMoulds]![fldSize]
        !fldBVPatt = [Forms]![frmMoulds]![fldBVPatt]
        !fldTransactionResult = "FAILED-MANUFACTURER"
        .Update
    End With

try this
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
What do I need to dim rst as?
0
 
rockiroadsCommented:
take from previous post

i.e.

    Dim rst As ADODB.Recordset
   
    set rst = New ADODB.Recordset


remember to close once done

rst.Close
set rst=Nothing


0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Thank you.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think it would be fair to reopen the question to give some points?
0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
Absolutely.
0
 
rockiroadsCommented:
Hi,
Thanks for the thought guys. I didn't expect that. I was helping out even though I knew the question was closed.

Main thing is, has RQuadling understood what Ive done and did it work okay ?

0
 
Richard QuadlingSenior Software DeverloperAuthor Commented:
I'm now half way through my conversion. I'll be trying the suggested mechanism next. I can't see why it won't work and it looks more like the rest of the code (so any sucker coming along to work with it may understand it better than my way).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now