Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using MS Access on OLE DB

Posted on 2010-08-31
17
Medium Priority
?
880 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 2
17 Comments
 
LVL 85
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 85
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

597 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