[RESOLVED] How to open *.mdb file(Access 2000 aplication) with VB6 code?

I have this method which is clicking on an image:

Code:

---

Private Sub Image7_Click()
    Dim ref As Reference
    Dim objaccess As Access.Application
    Dim errorMsg As String
    Dim methodName As String
    methodName = "Image7_Click"
   
    On Error GoTo appError
    StatusBar1.Panels(1).Text = ""
   
    Set objaccess = CreateObject("Access.Application")
    ' fails with error: Microsoft Access can't open the database because it is missing, or opened exclusively by another user., 7866, -1, MSAccess
    objaccess.OpenCurrentDatabase "MyDB.mdb"
   
    ' fails with error: You made an illegal function call., 7952, -1, MSAccess
    'For Each ref In objaccess.References
        'Debug.Print ref.FullPath
    'Next
    'objaccess.CloseCurrentDatabase

    objaccess.DoCmd.Maximize
   
    Exit Sub
appError:
    errorMsg = methodName & "(): " & Err.Description & ", " & CStr(Err.Number) & ", " & Err.HelpContext & ", " & Err.Source
    Debug.Print errorMsg
    StatusBar1.Panels(1).Text = errorMsg
End Sub

---

I am using MyDB.mdb in my ADODC components. Could this be the cause this method fails with error message:

Code:

---

Image7_Click(): Microsoft Access can't open the database because it is missing, or opened exclusively by another user., 7866, -1, MSAccess

---

If I uncomment the code for printing references to MyDB.mdb, it fails with error:

Code:

---

Image7_Click(): You made an illegal function call., 7952, -1, MSAccess

---

I also tired copying MyDB.mdb with other name and then using that other mdb which is not used by my ADODC components. I get still the same two errors I mentioned even with another mdb file.

What do i do wrong?

Do you want to automate MS-Access, or do you want to access a MDB?

Hmm, open the mdb and enable other users edit data in tables.

Also I might want run a macro.

Is there mixing the the approaches in the given code?

To access/edit data you can use ADO.
http://www.vbforums.com/showthread.p...ed-May-13-2011

What would be the need of executing embedded macros?

The one thing that jumps out at me is that you are not providing a path to the mdb, could be looking in the wrong place for it.

Hmm, the mdb is in the same folder where the form is(Form with Image7 object).

ONE way:

Code:

---

Dim cn As ADODB.Connection  'this is the connection object
    Dim rs As ADODB.Recordset  'this is the recordset object
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\generico.mdb"
    cn.Open
    Set cmd.ActiveConnection = cn
    cmd.CommandType = adCmdText
    cmd.CommandText = "select L, count(L) as myCount from abi_bper where s=? group by L"
    cmd.Parameters.Append cmd.CreateParameter("param1", adVarChar, adParamInput, 1, "0") 'OR, "0" could be a textbox value, etc
    Set rs = cmd.Execute
   
    Do While Not rs.EOF
        'do whatever you want with each or any record returned
        rs.MoveNext
    Loop

---

Quote:

---

Originally Posted by kutlesh

Hmm, open the mdb and enable other users edit data in tables.

Also I might want run a macro.

Is there mixing the the approaches in the given code?

---

Rather than opening an mdb which would be done via ADO with no need for any references to Access that you are trying to automate Access and have it open the mdb.

Your error message seems pretty clear either the mdb is already opened for exclusive access or it is not at the location the program is looking.
Make sure there are no copies of access running that may have the db locked, make sure that the db is where the program is looking for it.

No I am not talking to use RecordSet in order to open table in Access, I am saying that the Access 2000 software itself should open and load the mdb file, so other users can browse data in several tables, and edit them as needed.
Yes, I know I can for example, add another window with DataGrid which will mimic the table in Access 2000, but why bother?

By the way I need to easily export data from Access tables into Excel for further viewing and data checking.
The easiest way is to open Access 2000 itself, because the time is short and its just CTRL+A on Access table and copy-paste to Excel worksheet.

did you actually try the above suggestion of providing the full path to the .mdb file?
being in the same folder may not be enough for access to find it

Quote:

---

Originally Posted by kutlesh

No I am not talking to use RecordSet in order to open table in Access, I am saying that the Access 2000 software itself should open and load the mdb file, so other users can browse data in several tables, and edit them as needed.
Yes, I know I can for example, add another window with DataGrid which will mimic the table in Access 2000, but why bother?

By the way I need to easily export data from Access tables into Excel for further viewing and data checking.
The easiest way is to open Access 2000 itself, because the time is short and its just CTRL+A on Access table and copy-paste to Excel worksheet.

---

Well for one thing using ADO does not require the user to have Access installed on their system. Your method requires that they have Access 2000 installed which most users will not have that version if they have Office installed it is likely going to be a much newer version. In order to get your program to work with other versions it would need to be changed to use late binding.

Of course that has little to do with your issue and you have not said if you checked either of the things I mentioned before so not much more to suggest at this point.

Quote:

---

Originally Posted by kutlesh

No I am not talking to use RecordSet in order to open table in Access, I am saying that the Access 2000 software itself should open and load the mdb file, so other users can browse data in several tables, and edit them as needed.

---

Uhmm? ShellExecute?

I have seen that ShellExecute used to send keys, something like this:

Code:

---

Public Sub Sendkeys(text As Variant, Optional wait As Boolean = False)
    Dim errorMsg As String
    Dim methodName As String
    Dim WshShell As Object
   
    methodName = "Sendkeys"
   
    On Error GoTo errorPoint
   
    Set WshShell = CreateObject("wscript.shell")
    WshShell.Sendkeys CStr(text), wait
    Set WshShell = Nothing
   
    Exit Sub
errorPoint:
    errorMsg = methodName & "(): " & Err.Number & ", " & Err.Description
    Debug.Print errorMsg
   
End Sub

---

How can i use it to open Access 2000 mdb database?

Tried this, and it worked:

Code:

---

Private Sub Image7_Click()
    Dim access2000App As Object
    Dim access2000Path As String
    Dim errorMsg As String
    Dim methodName As String
    methodName = "Image7_Click"
   
    On Error GoTo errorPoint
    StatusBar1.Panels(1).text = ""
   
    access2000Path = "MSACCESS.exe """ & App.Path & "\MyDB.mdb"""
   
    Set access2000App = CreateObject("Wscript.shell")
    access2000App.Run access2000Path

    Exit Sub
errorPoint:
    errorMsg = methodName & "(): " & Err.Description & ", " & CStr(Err.Number) & ", " & Err.HelpContext & ", " & Err.Source
    Debug.Print errorMsg
    StatusBar1.Panels(1).text = errorMsg
End Sub

---

I had a problem with the quotes. Make sure the path of the actual mdb file is surrounded by quotes when you execute the Run method of Wscript.shell.

If you are satisfied that your issue is resolved, use the THREAD TOOLS menu option on this Forum to "Mark Thread As Resolved". That way, people won't 'waste' time to visit this thread to see if you need more help on it.

Sammi

EDIT: And don't forget to 'credit' those who helped you solve your issue(s) by clicking on the small * (star) in the post(s) of those who provided that help