Solved

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

Posted on 2006-06-22
20
646 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA 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…

733 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