Solved

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

Posted on 2006-06-22
20
634 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:RQuadling
  • 10
  • 7
20 Comments
 
LVL 40

Author Comment

by:RQuadling
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:RQuadling
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:RQuadling
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
 
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:RQuadling
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:RQuadling
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 40

Author Comment

by:RQuadling
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:RQuadling
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:RQuadling
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:RQuadling
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:RQuadling
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now