rsContacts.ActiveConnection = CurrentProject.Connection Crashes Access

I have a database that has work for years.  It just started to crash when it reaches the vb line

rsContacts.ActiveConnection = CurrentProject.Connection

Every time it gets to this line, Access crashes with Microsoft Access has stopped working with an option to search online for a solution or restart.

Any help would be appreciated
LVL 1
ScamquistAsked:
Who is Participating?
 
ScamquistConnect With a Mentor Author Commented:
Found a solution.  
Per Microsoft:
In a mixed Microsoft Office Access 2007 and Microsoft Office 2010 environment, you use the Microsoft.ACE.OLEDB.12.0 connection string through a Visual Basic for Applications (VBA) command to connect to Access 2007. In this situation, Access 2007 may crash.

Crash indeed.

The patch is located at

http://support.microsoft.com/kb/2512789

AngelIII thanks for the try
0
 
jerryb30Commented:
What has changed?
DB version?
OS?
Computer running the program?
Local database? Over a network?
Is Access a front end to another database?
0
 
ScamquistAuthor Commented:
Running on a terminal server.  Access 2007.  recently upgraded outlook to 2010 for a hosted exchange.  

The database is split.  Front and back end are on the same server.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
jerryb30Commented:
It is difficult to infer why a process which has been working stops suddenly from a single line of code.
I have no clues, and I am only asking questions so others may offer further suggestions/questions.
Does your process involve Outlook?
Does a local instance (not run on a terminal server) work?

I should have asked: Aside from what you mentioned on upgrade, has anything changed?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this line instead:
set rsContacts.ActiveConnection = CurrentProject.Connection

connection is a object, without the set (in vba), it will use the connection string property, which is just waste of resource as it will try to open another connection...
0
 
ScamquistAuthor Commented:
I used the line
set rsContacts.ActiveConnection = CurrentProject.Connection

Same problem.  I upgraded to Outlook 2010 because I began using a hosted Exchange.  Do you think it would help to upgrade the rest of the office suite to 2010 professional?
0
 
ScamquistAuthor Commented:
FYI, here are the preceeding lines

Public Function AppraiserBidEmail()
'Test if outlook is open.  If not, open it
Call IsOutlookOpen
Dim objOutlook As New Outlook.Application
Dim ObjEmail As Outlook.MailItem

Dim strLtrContent As String
Dim rsContacts As New ADODB.Recordset

rsContacts.ActiveConnection = CurrentProject.Connection
rsContacts.Open "tblRFB"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this one will be difficult to tackle, I fear.
maybe some project references are not working anymore, with the upgrade ...
can you check which version of ado is used in the project?
can you duplicate the problem on another computer (maybe a VM?)
0
 
ScamquistAuthor Commented:
How do I check the version of ado is used?

I copied the front and backend to my local computer and it did not fail.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
ScamquistAuthor Commented:
I ran the code.  Error:

Microsoft OLE DB Provider for SQL Server -->[DBNETLIB][ConnectionOpen)Connect()).]SQL Server does not exist or access denied.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
very good.
so, something has changed, which makes the connection string is not correct or needs to be changed.

you may want to check this article to troubleshoot the sql server connection:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1881-Connect-to-your-SQL-Server.html
0
 
ScamquistAuthor Commented:
the backend database is an accdb access 2007 file, not SQL.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error message tells you that you are trying to connect to a sql server, though.
please double-check the connection string you are using there.
0
 
ScamquistAuthor Commented:
Very strange.  This database has been working for a couple of years.  SQL was never used.  Are there connection strings when using linked tables?  

I also included the references in case there is something strange there.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that access project does use a connection string internally to connect to the sql server...
0
 
ScamquistAuthor Commented:
I forgot, there are a few tables that connect to a SQL server on my website.  How would I find the connection string?
0
 
ScamquistAuthor Commented:
BTW, I am able still able to open all of the tables that are connected to the web SQL server.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from within the access project?
if yes, this sounds very curious ...
0
 
ScamquistAuthor Commented:
The tables are linked via ODBC.  No problems opening.  Could there be a problem with a mix of references to Office 12 and Office 14?  It to too much of a coincidence that this started once outlook 2010 was installed.
Access-References.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good finding
0
 
ScamquistAuthor Commented:
My hardware support tech found a hot fix.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.