Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 7th, 2006, 06:35 AM
Authorized User
 
Join Date: Feb 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Connection between 2 MS Access Database

Hi, need some help here...

I am currently doing up a MS Access tool. (Coding in the VBA environment in MS Access)

Currently, I have 2 MS Access Database file (File1.mdb and File2.mdb).

From File1.mdb, I need to connect to File2.mdb to extract certain data (in Table2).
An ODBC Link using Data Source Name was set up and I used the following codes (in a form from File1.mdb)

================================================== ===
1 dim con as new adodb.connection
2 dim rec as new adodb.recordset

3 con.Open "DSN=TABLE_REQUIRED"
4 rec.Open "SELECT * FROM data", con, adOpenDynamic, adLockReadOnly

================================================== ===

I have no problems getting the connection up, but I keep getting the following error message which is not allowing me to open the recordset that I need:

[Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'data'.

I have alread enable all access rights for all user to File2.mdb
Hence, I am not sure why I still can't get the recordset opened!

Any help is greatly appreciated!



Scripts82
__________________
Scripts82
 
Old February 7th, 2006, 09:04 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Try passing the username and password for the second database, if there is one. It sounds like you have either Access security, or some network security running. Access security will take a username and password, but I am not sore how to pass it to network security.

Is the second database set to read-only through its network properties dialog box? Is it in a secure folder, etc?

You might also consider linking the databases by using the Insert > Table > Link Table wizard, and then just referring to the second database table as a local table.

HTH


mmcdonal
 
Old February 8th, 2006, 02:35 AM
Authorized User
 
Join Date: Feb 2006
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mmcdonal...
You are right about the access security thingy...
Somehow, after rebuilding the database on a fresh mdb file, the problem was solved...

Scripts82





Similar Threads
Thread Thread Starter Forum Replies Last Post
ado.net and ms access owoade .NET Web Services 1 August 31st, 2007 08:45 PM
Database migration MS Access 2003 to MS SQL 2000 ayazhoda SQL Server 2000 3 April 23rd, 2007 11:38 AM
problem using ADO to open form in MS Access apike VBScript 3 May 2nd, 2006 06:25 AM
ms access database connection android66 Javascript 1 December 1st, 2004 07:19 AM
Convert ADO connection to DAO database dr_morose Access VBA 0 November 17th, 2004 05:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.