Solved

Using MS Access on OLE DB

Posted on 2010-08-31
17
847 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
Comment Utility
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
Comment Utility
Via the menus. Not doing any code
0
 
LVL 84
Comment Utility
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
 
LVL 30

Author Comment

by:Mike Lazarus
Comment Utility
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
Comment Utility
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
Comment Utility
Yes, all the tests above were done on the client's PC via Goto Meeting
0
 
LVL 9

Expert Comment

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

Author Comment

by:Mike Lazarus
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 9

Expert Comment

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

Author Comment

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

Expert Comment

by:snurker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Solved as per comment.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now