Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-06-22
20
Medium Priority
?
655 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 2000 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 143

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

715 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