Transfer problem on Extra PC

  • Thread starter Thread starter Pete T
  • Start date Start date
P

Pete T

First I am very sorry we are using older software.

Using Extra Personal Client 6.5
Access 97
Windows 2000

I have a macro in Attachmates-Extra Personal Client Terminal Emulator
which selects out data on the screenm to be posted to a Database. I
can select out the data but accessing the Database from within Extra
has be a challenge. Here is my code, the errors start at rst.AddNew.

MyArea is a copied screen area of data.

' Transfer Code to Database

Dim db as object
Dim rst as object
Dim strCon as string
Dim strSQL as string

Set db=createobject("ADODB.CONNECTION")
Set rst=createobject("ADODB.Recordset")

strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\xxx\xxx\xxxx\WS Verification.mdb" ' Database is on another
PC.
db.open strCon
strSQL = "Select * FROM WS" ' WS is the Basic Entry Table

rst.open strSQL,db

rst.AddNew
rst.fields("SSNName") = MyArea
rst.fields("LO")= "6490"
rst.Update

Msgbox " Update Completed."

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

Appreciate any help, as this project "if it works" will go statewide.
Currently we have to copy the data to an Outlook form , then process
it to the database. Trying to cut out the middle man. Thanks
 
Check your references (From a code window: Tools ... References) and make
sure that ADO is checked. You'll have much better luck using DAO recordsets
against JET databases particularly an Access 97 database which was immature
with ADO. You might try something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDB
Set rst = db.OpenRecordset (strSQL)

rst.MoveLast ' I like to do this, humor me
rst.AddNew
rst.fields("SSNName") = MyArea
rst.fields("LO")= "6490"
rst.Update

Msgbox " Update Completed."

' Notice I am not closing the CurrentDB
rst.Close
Set rst = Nothing
Set db = Nothing
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
' Notice I am not closing the CurrentDB
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...

Hi Arvin,

I have a few questions here for you, or anyone else who wishes to answer. I noticed your
comment above about not closing the CurrentDB, but you don't say why this is so. I guess
it's okay to set it to Nothing without closing it first?

How important is it to set db = Nothing?
KB articles http://support.microsoft.com/?id=289562 (Access 2002) and
http://support.microsoft.com/?id=209847 (Access 2000), which deal with the topic "To Help
Prevent Database Bloat, Explicitly Close Recordsets", indicate that one should close
recordsets and querydefs, but they make no mention of either closing the CurrentDB or
setting it to Nothing.

So this issue seems to be a little confusing to me.

Thanks,
Tom
__________________________________


Check your references (From a code window: Tools ... References) and make
sure that ADO is checked. You'll have much better luck using DAO recordsets
against JET databases particularly an Access 97 database which was immature
with ADO. You might try something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDB
Set rst = db.OpenRecordset (strSQL)

rst.MoveLast ' I like to do this, humor me
rst.AddNew
rst.fields("SSNName") = MyArea
rst.fields("LO")= "6490"
rst.Update

Msgbox " Update Completed."

' Notice I am not closing the CurrentDB
rst.Close
Set rst = Nothing
Set db = Nothing
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi Arvin,
I have a few questions here for you, or anyone else who wishes to answer. I noticed your
comment above about not closing the CurrentDB, but you don't say why this is so. I guess
it's okay to set it to Nothing without closing it first?

How important is it to set db = Nothing?
KB articles http://support.microsoft.com/?id=289562 (Access 2002) and
http://support.microsoft.com/?id=209847 (Access 2000), which deal with the topic "To Help
Prevent Database Bloat, Explicitly Close Recordsets", indicate that one should close
recordsets and querydefs, but they make no mention of either closing the CurrentDB or
setting it to Nothing.

So this issue seems to be a little confusing to me.
Click to expand...

VB/VBA doesn't always clean up after itself so that object variable will on
very rare occasions fail to go out of scope. When it happens on the
recordset object, there can be a memory leak, and/or excessive bloat, and/or
Access itself will not close (although the application appears to)

For that reason, it is always considered best practice to close and set to
nothing any object variable that you open. The exception is the currentdb
object which is in use. The variable should be set to nothing, but you don't
close the currentdb. What happens if you do? Nothing as far as I can see,
but why tempt fate?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thank You for your answer. I just noticed a similar question posted on 11/1/2003 with
subject "Why using .Close AND =Nothing?", with answers by Dirk Goldgar and someone who
calls themselves "help".

________________________________________

Arvin Meyer said:
Hi Arvin,

I have a few questions here for you, or anyone else who wishes to answer. I noticed your
comment above about not closing the CurrentDB, but you don't say why this is so. I guess
it's okay to set it to Nothing without closing it first?

How important is it to set db = Nothing?
KB articles http://support.microsoft.com/?id=289562 (Access 2002) and
http://support.microsoft.com/?id=209847 (Access 2000), which deal with the topic "To Help
Prevent Database Bloat, Explicitly Close Recordsets", indicate that one should close
recordsets and querydefs, but they make no mention of either closing the CurrentDB or
setting it to Nothing.

So this issue seems to be a little confusing to me.
Click to expand...

VB/VBA doesn't always clean up after itself so that object variable will on
very rare occasions fail to go out of scope. When it happens on the
recordset object, there can be a memory leak, and/or excessive bloat, and/or
Access itself will not close (although the application appears to)

For that reason, it is always considered best practice to close and set to
nothing any object variable that you open. The exception is the currentdb
object which is in use. The variable should be set to nothing, but you don't
close the currentdb. What happens if you do? Nothing as far as I can see,
but why tempt fate?
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks Arvin, however the Macro window for the Terminal Emulator
program (Extra PC) will not allow anything except objects, strings &
integers. It errors if you attempt to use Database or Recordset.
Thus you have to use Dim db as object, etc.
 
I use Terminal Server with Access 2002 MDBs all the time, and I know for a
fact that dimming as a Database and Recordset do work. The reason you are
getting an error is because you have a missing reference, probably on the
server. Either the reference is in another place, (the more likely reason)
or it is missing altogether. Fix the references and try the DAO code. It
should work just fine.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top