Create a new workgroup information file in Access 2010

[AccJohn]

Hi all! I'm an access-beginner and have the following problem:
We were using Access 2003 with an ODBCDirect database and upgraded to Access 2010 recently. After the upgrade I received an error message that ODBCDirect isn't supported anymore and that I have to change from DAO to ADO in the corresponding source code parts. After receiving this message I changed the VBA source code of the database connection.

I'm handling the db connection now over an ADODB.Connection object by defining the connectionString and not using ODBCDirect objects anymore. Now I'm receiving the message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

I can't find this workgroup information file anywhere. My colleague told me, that they never used such a file.

My database has the type .mdb.

How can I create a new workgroup information file in Access 2010?
I found several tutorials in forums but none of them is applicable to my problem.
For example.. I have no tools/security menu and also no WorkgroupAdministrator settings in my Access2010 ... or it is hidden very well in the new menu layout of Access 2010.

Thanks for reading
AccJohn

 

[Ranman256]

Access 2010 changed it.
see:
http://www.addintools.com/documents/access/where-is-workgroup-manager.html

 

[AccJohn]

Access 2010 changed it.
see:

Thanks for the quick reply!
I know this link. The problem is: it is very difficult for me to install new addins, software etc on my computer due to our strict security policy.

Is there no other way to solve this problem?

 

[vbaInet]

Run the following command:

Docmd.RunCommand acCmdWorkgroupAdministrator

 

[AccJohn]

Run the following command:

Docmd.RunCommand acCmdWorkgroupAdministrator

Thanks a lot! I'm (hopefully) some steps further now. But unfortunatelly I'm receiving a new error message.

(I don't want to open a new thread because I want to retain the overall context , but if you want me to, then I will open one).

When I'm running my Access2010 Application I always receive the error message "operation isn't supported for this type of object". After debugging via commenting out and running the program I ((hopefully) found out that the Open - method of the connection object is causing the error message.

Here the relevant source code:

Global conWork As ADODB.Connection
...
Set conWork = New ADODB.Connection 
... 
conWork.ConnectionString = "ODBC;DRIVER={SQL Server};SERVER=someServer.x.y.z;Provider=Microsoft.ACE.OLEDB.12.0;UserID=user;Password=pw;Data Source=someServer.x.y.z; Trusted_Connection=yes;"
...
conWork.Open  '...causes the error msg "OPERATION IS NOT SUPPORTED FOR THIS TYPE OF OBJECT"

In order to open a new database connection I followed several relevant tutorials... but it's still not working. I don't get it.. the "Open" is a valid method of the conWork-object.. even the vba-editor automatically suggests me this option by the autocomplete-functionality.

In the vba editor I have the Microsoft ADO 2.8 Library and the Microsoft ADO 2.8 RecordSet Libary selected in the references-settings.

Best regards
AccJohn

 

[JLCantara]

@RanMan post #2: beware I bought this think and found it's lousy piece of scrap. Since the guy is parked in Hainan, I plan a trip to China to kick his butt...

 

[vbaInet]

Your connection string is muddled up - it's mixing ODBC SQL Server with Access. Are you trying to open an Access db or a SQL Server db instance?

And I see no where in your code where conWork is initialised.

 

[AccJohn]

Your connection string is muddled up - it's mixing ODBC SQL Server with Access. Are you trying to open an Access db or a SQL Server db instance?

And I see no where in your code where conWork is initialised.

Sorry, forgot to put the line of the conWork initialisation. Edited my previous post now.
I am using an .mdb file.

I changed my connectionString to :

conWork.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};SERVER=server.x.y.z;Provider=Microsoft.ACE.OLEDB.12.0;UserID=somebody;Password=password;Data Source=server.x.y.z;"

If I write "ODBC;" at the very beginning of this connectionString then I receive the error message "You cannot use ODBC to import from, export to, or link an external MS Access or ISAM database table to your database." each time I run the application.

If I'm using this connectionString without "ODBC;" then I receive the error message "could not find installable ISAM".

 

[vbaInet]

You're mixing ODBC with OLEDB. Where are you getting these connection strings from?

See here:
https://www.connectionstrings.com/microsoft-access-accdb-odbc-driver/

 

[AccJohn]

You're mixing ODBC with OLEDB. Where are you getting these connection strings from?

See here:
[link]

Thanks a lot! Now the db connection is working I think.

But I have a new problem.

Here is my ADODB.Connection.connectionString:

    "Driver={Microsoft Access Driver (*.mdb)};Dbq=\\folder1\folder2\User1\Database.mdb; Uid=Admin;Pwd=password;"

After updating the Database-connection I have also to update the sql command object. Therefore I am initializing an ADODB.Command object:

Dim qdfWork As ADODB.Command
    ...
    Set qdfWork = New ADODB.Command
    Set qdfWork.ActiveConnection = CurrentProject.Connection
    qdfWork.CommandText = "[dbo].[storedProcedureName]"
    qdfWork.CommandType = adCmdStoredProc
    qdfWork.Parameters.Refresh  'HERE THE ERROR-MESSAGE OCCURS
    ...

There is a stored procedure with the exactly same name "[dbo].[storedProcedureName]" stored on the server, but I still get the error-message:

"the microsoft access database engine cannot find the input table or query 'dbo'. Make sure it exists and that its name is spelled correctly."

If I don't use "[dbo]" in the CommandText I still get the same message, telling that "storedProcedureName" can't be found.

The connection via this connectionString works fine I think, I don't get any error-messages when connecting to the DB.

I checked that CurrentProject.Connection is really the connection I need.

I don't understand why my application can't find this stored procedure although it is stored on the server.

thx for reading