Solved

Using MS Access on OLE DB

Posted on 2010-08-31
17
859 Views
Last Modified: 2012-05-10
Trying to access an ACT! by Sage database using SQL 2008 via OLE DB.

Using MS Excel, all is fine, but when using MS Access, I get: The OLE DB Provider “ACT! OLE DB Provider for Reporting 2.0” has not been registered

Any idea what needs to be registered in MS Access that's different from Excel?
0
Comment
Question by:Mike Lazarus
  • 10
  • 5
  • 2
17 Comments
 
LVL 84
ID: 33566148
There shouldn't be any difference. If the provider is registered on your machine, it should be registered for all products.

How are you building the connection? Are you using File - External DAta (or equivalent in 2007), or are you building the connection by hand in code?
0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33566188
Via the menus. Not doing any code
0
 
LVL 84
ID: 33566236
I'd try reinstalling the driver first. May have just not registered itself properly, and Excel might be less picky about that sort of thing than is Access.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33566257
Thanks ... I'll give it a try.

I'd also assumed that, if it worked for one, it should work for the other. But, as I don't use Access (it's on a client PC) I wanted to be sure before pulling stuff apart
0
 
LVL 9

Expert Comment

by:snurker
ID: 33566410
you may need to verify the same driver that you have on your machine is on the client's. Have you tried pulling the data via Excel on the client's PC?
0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33566547
Yes, all the tests above were done on the client's PC via Goto Meeting
0
 
LVL 9

Expert Comment

by:snurker
ID: 33566798
Were you using the system DSN for both or the User DSN?
0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33566834
Not sure what a user DSN is.... sorry.

Basically used this process:
http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=25186
0
 
LVL 9

Expert Comment

by:snurker
ID: 33566943
ok. How are you attempting to connect with Access?
0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33567027
Pretty much the same... get that error after clicking "Finish" (item 8 on that KB article)
0
 
LVL 9

Expert Comment

by:snurker
ID: 33567849
Access does not have connection manager.

Have you tried to use the SQL native client DSNs to connect to the server in Access? You will find these by going to External Data>more>ODBC Database. Click to link the table. Click Machine data Source. If you do not see SQL Native Clietn, click new and add it. The driver will be at the bottom. These are the instructions from 2007. 2003 should be similar.
0
 
LVL 9

Expert Comment

by:snurker
ID: 33567883
Here is linking to an SQL server fromt he help in Access. It looks prettier.

Link to the data Open the destination database.
On the External Data tab, in the Import  group, click More.
Click ODBC Database.
Click Link to the data source by creating a linked table,  and then click OK.
In the Select Data Source dialog box, click the .dsn file  that you want to use, or click New to create a new data source  name (DSN).
In the Select Data Source dialog box, if the .dsn file you  want to use already exists, click the file in the list.  
I need to  create a new .dsn file  
 Note    The steps in this procedure might vary slightly  for you, depending on the software that is installed on your computer. Click New to create a new data source name (DSN).
The Create New Data Source Wizard starts.
In the wizard, select SQL Server in the list of drivers, and  then click Next.
Type a name for the .dsn file, or click Browse to save the  file to a different location.
 Note    You must have write permissions to the folder  to save the .dsn file.
Click Next, review the summary information, and then click  Finish to complete the Create New Data Source Wizard.
The Create a New Data Source to SQL Server Wizard starts.
In the wizard, type a description of the data source in the Description box. This step is optional.
Under Which SQL Server do you want to connect to, in the Server box, type or select the name of the SQL Server computer to  which you want to connect, and then click Next to continue.
On this page of the wizard, you might need to get information from the SQL  Server database administrator, such as whether to use Windows NT authentication  or SQL Server authentication. Click Next to continue.
On the next page of the wizard, you might need to get more information from  the SQL Server database administrator. If you want to connect to a specific  database, ensure that the Change the default database to check  box is selected, select the SQL Server database that you want to work with, and  then click Next.
Click Finish. Review the summary information, and then click  Test Data Source.
Review the test results, and then click OK to close the SQL Server ODBC Data Source Test dialog box.
If the test was successful, click OK again to complete the  wizard, or click Cancel to return to the wizard and make changes  to your settings.
   
Click OK.
Access displays the Link Tables dialog box.
Under Tables, click each table or view that you want to link  to, and then click OK.
If the Select Unique Record Identifier dialog box appears,  Access was unable to determine which field or fields uniquely identify each row  of the source data. In this case, select the field or combination of fields that  is unique for each row, and then click OK. If you are not sure,  check with the SQL Server database administrator.
 
Access completes the linking operation and displays the new linked table or  tables in the Navigation Pane.
Important  Each time you open either a linked table  or the source object, you see the latest data displayed in it. However,  structural changes made to a SQL Server object are not automatically reflected  in a linked table.
To update a linked table by applying the latest SQL Server object  structure: Right-click the table in the Navigation Pane, and then click Linked Table Manager on the shortcut menu.
Select the check box next to each linked table that you want to update, or  click Select All to select all of the linked tables.
Click OK.
If the update is successful, Access displays a message to that effect.  Otherwise, Access displays an error message.
Click Close to close the Linked Table Manager

0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33569837
We'd prefer to use OLEDB - this includes the views for the database and is much easier to use
0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33572564
Same error from multiple machines and local database.... all work with Excel, none with Access.

Also tried re-registering the OLE DB -  
Ran regsvr32 on server and client (both actoledb.dll and actoledb2.dll)  

BTW: If any help:
If, in the first step, I have chosen to create a “Linked Server” connection, I get the message “The OLE DB Provider “ACT! OLE DB Provider for Reporting 2.0” has not been registered”.  
If, in the first step, I have chosen to create a “Transact LQL” connection, I get the message “An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias values. Aliases defined as “” or [] are not allowed. Change the alias to a valid name”.  


0
 
LVL 30

Author Comment

by:Mike Lazarus
ID: 33881471
Found a bit more on this ...

The Access project is an ADP using SQL... we can't even open an Excel file via OLEDB

Can anyone confirm that an Access ADP using Excel can't open other OLEDB providers?
0
 
LVL 30

Accepted Solution

by:
Mike Lazarus earned 0 total points
ID: 34675997
Sorry for the delay in responding. It seems that Access can't connect to a 64bit SQL via OLEDB

Thanks Microsoft!
0
 
LVL 30

Author Closing Comment

by:Mike Lazarus
ID: 34732426
Solved as per comment.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 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