Converting Access XP to Access 2003 (Run-Time error 2004)

M

Matt

Our company are migrating an MS Office XP database to Access2003. The
database file format is already in 2002 format so I didn't expect much
trouble, however we use the database as a container for forms and
connect to our back end using ODBC Direct. I have a very strange
problem in that if I retrieve a SQL 'text' field which contains null
and try and put the result in an unbound text box and then try and use
that value ie:

txtBoxCustomerName = rstCustomers![CustomerName]
Debug.Print txtBoxCustomerName

I get the following error:

"Run-time error '2004':
There isn't enough memory to perform this operation.
Close unneeded programs and try the operation again."


Our problem can be simulated by connecting to the Northwind database
with this snippet of code, which works fine in VB6, AccessXP. I can
even code put it in our access2003 database save and then open in
access XP and it work, but running in Access 2003 errors:

' Notes:
' Requires connection suitable to point to a SQL MS Northwind database
and a textbox on the form called txtResult
Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

Const c_sServerName As String = "SERVER-SQL"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL
Server};SERVER=" & c_sServerName &
";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees WHERE
(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error
2004 as above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub


Please could anyone try this sample piece of code and see if they can
reproduce the bug.

Client Machine Setup:
WindowsXP SP2
MSAccess 2003 Pro SP2
DAO360

Server
MS SQL 7 OR MS SQL 2005


Thanks Matt
 
B

Brendan Reynolds

I can't reproduce this, Matt. I copied and pasted your code, and it runs
without error for me. The only thing I changed was the server name, like so
....

Option Compare Database
Option Explicit

Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

'Const c_sServerName As String = "SERVER-SQL"
Const c_sServerName As String = "(local)"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL Server};SERVER=" &
c_sServerName & ";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees
WHERE(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error2004 as
above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub

Private Sub Command2_Click()
LoadANullTextField
End Sub

--
Brendan Reynolds
Access MVP

Matt said:
Our company are migrating an MS Office XP database to Access2003. The
database file format is already in 2002 format so I didn't expect much
trouble, however we use the database as a container for forms and
connect to our back end using ODBC Direct. I have a very strange
problem in that if I retrieve a SQL 'text' field which contains null
and try and put the result in an unbound text box and then try and use
that value ie:

txtBoxCustomerName = rstCustomers![CustomerName]
Debug.Print txtBoxCustomerName

I get the following error:

"Run-time error '2004':
There isn't enough memory to perform this operation.
Close unneeded programs and try the operation again."


Our problem can be simulated by connecting to the Northwind database
with this snippet of code, which works fine in VB6, AccessXP. I can
even code put it in our access2003 database save and then open in
access XP and it work, but running in Access 2003 errors:

' Notes:
' Requires connection suitable to point to a SQL MS Northwind database
and a textbox on the form called txtResult
Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

Const c_sServerName As String = "SERVER-SQL"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL
Server};SERVER=" & c_sServerName &
";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees WHERE
(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error
2004 as above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub


Please could anyone try this sample piece of code and see if they can
reproduce the bug.

Client Machine Setup:
WindowsXP SP2
MSAccess 2003 Pro SP2
DAO360

Server
MS SQL 7 OR MS SQL 2005


Thanks Matt
Click to expand...
 
B

Brendan Reynolds

Correction, I can reproduce it. There wasn't any record in my Northwind
Employees table with EmployeeID = 10. I added a new record, with a Null
Notes field, and changed the SQL statement to select that record. Now I can
reproduce the error. A possible work-around is to append an empty string
like so ...

txtResult = rEmployee![Notes] & vbNullString

I don't get the error after making this modification.

--
Brendan Reynolds
Access MVP

Brendan Reynolds said:
I can't reproduce this, Matt. I copied and pasted your code, and it runs
without error for me. The only thing I changed was the server name, like so
...

Option Compare Database
Option Explicit

Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

'Const c_sServerName As String = "SERVER-SQL"
Const c_sServerName As String = "(local)"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL Server};SERVER=" &
c_sServerName & ";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees
WHERE(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error2004
as above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub

Private Sub Command2_Click()
LoadANullTextField
End Sub

--
Brendan Reynolds
Access MVP

Matt said:
Our company are migrating an MS Office XP database to Access2003. The
database file format is already in 2002 format so I didn't expect much
trouble, however we use the database as a container for forms and
connect to our back end using ODBC Direct. I have a very strange
problem in that if I retrieve a SQL 'text' field which contains null
and try and put the result in an unbound text box and then try and use
that value ie:

txtBoxCustomerName = rstCustomers![CustomerName]
Debug.Print txtBoxCustomerName

I get the following error:

"Run-time error '2004':
There isn't enough memory to perform this operation.
Close unneeded programs and try the operation again."


Our problem can be simulated by connecting to the Northwind database
with this snippet of code, which works fine in VB6, AccessXP. I can
even code put it in our access2003 database save and then open in
access XP and it work, but running in Access 2003 errors:

' Notes:
' Requires connection suitable to point to a SQL MS Northwind database
and a textbox on the form called txtResult
Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

Const c_sServerName As String = "SERVER-SQL"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL
Server};SERVER=" & c_sServerName &
";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees WHERE
(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error
2004 as above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub


Please could anyone try this sample piece of code and see if they can
reproduce the bug.

Client Machine Setup:
WindowsXP SP2
MSAccess 2003 Pro SP2
DAO360

Server
MS SQL 7 OR MS SQL 2005


Thanks Matt
Click to expand...
Click to expand...
 
M

Matt

Correction, I can reproduce it. There wasn't any record in my Northwind
Employees table with EmployeeID = 10. I added a new record, with a Null
Notes field, and changed the SQL statement to select that record. Now I can
reproduce the error. A possible work-around is to append an empty string
like so ...

txtResult = rEmployee![Notes] & vbNullString

I don't get the error after making this modification.
Click to expand...

Thanks Brendan.

For taking the time to reproduce it I thought I was going crazy, it
seems such an obvious thing that someone would do yet there doesn't
appear to be any hits on google. I have constructed a couple of
ways that i can avoid the bug including your suggestion:

txtResult.SetFocus
txtResult.Text = rstEmployee![Notes]

However our SQL Server database of well over 200 tables included are
69 text columns. Of which I would have to search through our
database of approximate 120 forms / 120 modules & classes isn't going
to be simple task and we could be chasing this for a while.

Would you agree that this is a bug in Microsoft Office 2003? A text
box can store a NULL value and works in previous versions and this
exact database WORKS if loaded in Office 2002. Also if you link this
table and use the linked table via DAO / ADO or any other method to
get at the data its fine and store it in the textbox.

Matt
 
B

Brendan Reynolds

It certainly looks like a bug, and the fact that it works in Access 2002 but
not in Access 2003 would seem to point the finger toward Access 2003, though
I'm not sure it is conclusive proof. (For example, when testing under Access
2002 and 2003, are identical builds of JET and the ODBC components involved?
If not, the bug could possibly be in one of those components.)

It might be worth posting a question in the
microsoft.public.access.odbcclientsvr newsgroup. There are some people who
are very knowledgeable about ODBC who might be more likely to see it there.

--
Brendan Reynolds
Access MVP

Matt said:
Correction, I can reproduce it. There wasn't any record in my Northwind
Employees table with EmployeeID = 10. I added a new record, with a Null
Notes field, and changed the SQL statement to select that record. Now I
can
reproduce the error. A possible work-around is to append an empty string
like so ...

txtResult = rEmployee![Notes] & vbNullString

I don't get the error after making this modification.
Click to expand...

Thanks Brendan.

For taking the time to reproduce it I thought I was going crazy, it
seems such an obvious thing that someone would do yet there doesn't
appear to be any hits on google. I have constructed a couple of
ways that i can avoid the bug including your suggestion:

txtResult.SetFocus
txtResult.Text = rstEmployee![Notes]

However our SQL Server database of well over 200 tables included are
69 text columns. Of which I would have to search through our
database of approximate 120 forms / 120 modules & classes isn't going
to be simple task and we could be chasing this for a while.

Would you agree that this is a bug in Microsoft Office 2003? A text
box can store a NULL value and works in previous versions and this
exact database WORKS if loaded in Office 2002. Also if you link this
table and use the linked table via DAO / ADO or any other method to
get at the data its fine and store it in the textbox.

Matt
Click to expand...
 
M

Matt

It certainly looks like a bug, and the fact that it works in Access 2002 but
not in Access 2003 would seem to point the finger toward Access 2003, though
I'm not sure it is conclusive proof. (For example, when testing under Access
2002 and 2003, are identical builds of JET and the ODBC components involved?
If not, the bug could possibly be in one of those components.)

It might be worth posting a question in the
microsoft.public.access.odbcclientsvr newsgroup. There are some people who
are very knowledgeable about ODBC who might be more likely to see it there.
Click to expand...

I have cross-posted into the other group and as such re-included the
full explanation again to keep both threads in sync.

Both comparison machines are Windows XP SP2 and detect as MDAC 2.8 SP2
using the component checker however i do have two mismatch's on the
machine that works:

MSADCO.DLL Expected 2.8.1117.0 Found 2.81.1124.0
msxml3.dll Expected 8.50.2162.0 Found 8.70.1104.0

Im not exactly sure where to look for the ODBC driver version. In
Administrative Tools\Data Sources (ODBC) clicking add and looking to
the connect type i have (SQL Server). Both 2000.85.1117.00

Matt



Our company are migrating an MS Office XP database to Access2003. The
database file format is already in 2002 format so I didn't expect much
trouble, however we use the database as a container for forms and
connect to our back end using ODBC Direct. I have a very strange
problem in that if I retrieve a SQL 'text' field which contains null
and try and put the result in an unbound text box and then try and use
that value ie:

txtBoxCustomerName = rstCustomers![CustomerName]
Debug.Print txtBoxCustomerName

I get the following error:

"Run-time error '2004':
There isn't enough memory to perform this operation.
Close unneeded programs and try the operation again."


Our problem can be simulated by connecting to the Northwind database
with this snippet of code, which works fine in VB6, AccessXP. I can
even code put it in our access2003 database save and then open in
access XP and it work, but running in Access 2003 errors (Please note
you may need to change the select statement so that it selects an
Employee with a NULL notes field):

' Notes:
' Requires connection suitable to point to a SQL MS Northwind database
and a textbox on the form called txtResult
Private Sub LoadANullTextField()
Dim wSQL As DAO.Workspace
Dim cSQL As DAO.Connection

Dim rEmployee As Recordset

Const c_sServerName As String = "SERVER-SQL"
Const c_sServerConnection As String = "ODBC;DRIVER={SQL
Server};SERVER=" & c_sServerName &
";Trusted_Connection=yes;database=Northwind"
Const c_sSelectStatement As String = "SELECT * FROM employees WHERE
(EmployeeID = 10)"


Set wSQL = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set cSQL = wSQL.OpenConnection("", dbDriverNoPrompt, False,
c_sServerConnection)

If cSQL Is Nothing Then
MsgBox ("Failed to connect to Sql Server : " & c_sServerName)
Else
'Find an employee where we know the notes field is null
Set rEmployee = cSQL.OpenRecordset(c_sSelectStatement,
dbOpenSnapshot)

If Not rEmployee.EOF Then
txtResult = rEmployee![Notes]

' Now txtResult contains duff info and will produce error
2004 as above.
Debug.Print txtResult
End If

rEmployee.Close
cSQL.Close
End If
End Sub
 
M

Matt

I have now submitted this issue to Microsoft and received a case
number, so far the issue has been replicated, apparently no-one has
reported this bug to them before!

Matt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2003 SP3 Run-time error 2004 1
Converting from Access to MS SQL Server 5
Run SQL Server stored procedure in Excel macro 1
Converting Varchar Hyperlink address from SQL Server to real hyperlink in MS Access 3
Access 2002 generates run time error 3157 ODBC update failed 2
Using Access 2007 to connect to SQL 2
Performing a Query against Access 2003 from SQL Server 64 bit? 1
Run-Time Error 3157 - Link SQL Table ODBC- Failed 4

Top