?
Solved

Connect an Ms access 2010 dbase to a VB6 project issue

Posted on 2012-08-17
9
Medium Priority
?
846 Views
Last Modified: 2012-08-25
Hello all

I used to use this code bellow for accessing an access 2007 dbase but it looks like 2010 is not the same.
I'm also on windows 7 64

I have this error:
Runtime Error 3709
The Connection Cannot be used to perform this kind of operation.
it is either closed or invalid in this context


I think the issue is with this part of the code but i dont know why:
oRST1.Open sSQL1, oConnect1

Open in new window


Can you please help me?

Thanks again

Dim sSQL1 As String
  Dim oConnect1 As ADODB.Connection
  Set oConnect1 = New ADODB.Connection
  Dim oRST1 As ADODB.Recordset
  Set oRST1 = New ADODB.Recordset


sSQL1 = "SELECT DISTINCT[nom]FROM [Clients]'"

oConnect1 = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Form2.txtBaseDelabsolution.Text

nom.AddItem ""
oRST1.Open sSQL1, oConnect1
Do Until oRST1.EOF
  nom.AddItem oRST1("Nom")
  oRST1.MoveNext
Loop

Open in new window

0
Comment
Question by:Wilder1626
  • 5
  • 2
  • 2
9 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 38305949
I have just tried like this and same issue:
Dim Conn As ADODB.Connection
Dim Conns As String
Dim oRST1 As ADODB.Recordset
Set oRST1 = New ADODB.Recordset
  
Set Conn = New ADODB.Connection
Conns = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Form2.txtBaseDelabsolution.Text
Conn.ConnectionString = Conns
'Conn.Open
nom.AddItem ""
oRST1.Open Conns, Conn
Do Until oRST1.EOF
  nom.AddItem oRST1("Nom")
  oRST1.MoveNext
Loop

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38306576
I tried also like this but now, i have "Run time error 3706 Provider cannot be found, it may not be properly installed"


im Conn As ADODB.Connection
Dim Conns As String
Dim oRST1 As ADODB.Recordset
Set oRST1 = New ADODB.Recordset
  
Set Conn = New ADODB.Connection
Conns = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Form2.txtBaseDelabsolution.Text
Conn.ConnectionString = Conns
Conn.Open
nom.AddItem ""
oRST1.Open Conns, Conn
Do Until oRST1.EOF
  nom.AddItem oRST1("Nom")
  oRST1.MoveNext
Loop

Open in new window

0
 
LVL 17

Expert Comment

by:inthedark
ID: 38307804
The fastest was to solve this is to install Just Access 2003 from an office CD in compatibility mode the you can open the database in VB6 as if you were running on XP or Win 7 32 bit.

Or see this post on Microsoft.com:

http://answers.microsoft.com/en-us/windows/forum/windows_7-windows_programs/msjet40-on-win7-64bit-cant-find-providers/848b8ce9-3f92-491b-8f28-4e3927e5fdb7?msgId=e1b9ad55-026e-4811-9fad-de0686431883
.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Author Comment

by:Wilder1626
ID: 38307816
But once the project compiled, will it work on another PC with windows 7 and Access 2010? or will i have to again install Access 2003 in compatibility mode?
0
 
LVL 17

Assisted Solution

by:inthedark
inthedark earned 1000 total points
ID: 38308264
How could Microsoft be so callous as to pull the plug on a technology that people have used since the early nineties.  Furthermore, there is no reason why a 64 bit version of jet could not have been created, it just needed one man to make it happen.  Microsoft were gambling that people would migrate their applications to server based technologies like SQ Server.

The simple reality is that if you need to change your code you may as well move away from Microsoft products and develop a non Microsoft solution.

Companies that tend to ignore their customer's needs usually suffer.  Take a look at what happened to IBM in the early 1980s.  They squandered their market lead and were shunned by the masses who vowed that whatever they did it would not involve IBM. It looks like Microsoft are in the process of doing the same.

Sorry I cannot be more helpful.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38308297
are you running in compatibility mode?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38308499
Hello all

No, not yet, I,m wondering if i should stay with office 2007 or switch to 2010.

I was looking on the web for the compatibility mode and they just talk about Excel, Word and PowerPoint. Not access.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 1000 total points
ID: 38308567
since VB6 produces a 32-bit executable, you can only use 32-bit ODBC drivers.  I suspect that your application installation needs some tweaking in order to place the DLLs in the 32-bit system folder and register them with regsvr32.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38333394
Thanks again for your help. For now, i will stay with Office 2007 and work for another solution after.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

864 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