Solved

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

Posted on 2006-06-22
20
640 Views
Last Modified: 2013-12-05
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
Comment
Question by:Richard Quadling
  • 10
  • 7
20 Comments
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958220
I found RunSQL as a method for DoCmd, but ...

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

0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958288
Oh. I realise that the parameters for the format of time is the wrong way round. Fixed.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958365
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16958402
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16958415
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958422
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16958430
ignore my code attempt
stick with insert, its better
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958432
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16958434
RQualiding, this wasn't about the points, as I already saw you had sorted it out
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958444
Ok.

I'd probably care more if I wasn't going to be rewriting the whole thing anyway.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 16958454
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16958536
What do I need to dim rst as?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16959418
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16959455
Thank you.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16959540
I think it would be fair to reopen the question to give some points?
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16959800
Absolutely.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16961340
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 16966825
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question