Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Using MS Access on OLE DB

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
Mike Lazarus
Asked:
Mike Lazarus
  • 10
  • 5
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
Via the menus. Not doing any code
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Technology Partners: 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!

 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
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
 
snurkerCommented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
Yes, all the tests above were done on the client's PC via Goto Meeting
0
 
snurkerCommented:
Were you using the system DSN for both or the User DSN?
0
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
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
 
snurkerCommented:
ok. How are you attempting to connect with Access?
0
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
Pretty much the same... get that error after clicking "Finish" (item 8 on that KB article)
0
 
snurkerCommented:
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
 
snurkerCommented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
We'd prefer to use OLEDB - this includes the views for the database and is much easier to use
0
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
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
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
Sorry for the delay in responding. It seems that Access can't connect to a 64bit SQL via OLEDB

Thanks Microsoft!
0
 
Mike LazarusAct! Evangelist - CRM ConsultantAuthor Commented:
Solved as per comment.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now