Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel 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 August 17th, 2005, 09:47 AM
Registered User
Join Date: Aug 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default db connection

I have an application in Excel that queries an Access database. Some users have read-only access to the folder that the application is saved in. These users are not able to run the application as it errors out when referencing the database to open it to query. I do not want to grant all users READ/WRITE access. Is there another way around this? The application errors out at the last line of code that is pasted below.
Thank you!

    Dim wrkJet As Workspace
    Dim dbsFinance As Object
    'Dim conPubs As Connection
    Dim rstTemp As Object
    Dim strSQL As String
    Dim SearchOption As Integer


'Open Microsoft Jet and ODBCDirect workspaces, Microsoft
    ' Jet database, and ODBCDirect connection.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

    'Enter the location of the database below
    Set dbsFinance = wrkJet.OpenDatabase("C:\Feuerborn\Account-Level Rptg Tool\Acct-Lvl Rptg Tool.mdb")
Old August 19th, 2005, 02:24 AM
Friend of Wrox
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts

I have users with read only access connecting to an database, I however prefer to use the ADO connectivity method, it allows much more versitility than the ODBC connections.

here how I would do it.

Sub temp()

Dim myADOConn As ADODB.Connection
Dim myADORs As ADODB.Recordset
Dim mySQL As String
Dim myLoop As Integer
Dim myConn As String

myConn = "DSN=MS Access Database;" & _
        "DBQ=C:\Feuerborn\Account-Level Rptg Tool\Acct-Lvl Rptg Tool.mdb;" & _
        "DefaultDir=C:\Feuerborn\Account-Level Rptg Tool;" & _
        "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    Set myADOConn = New ADODB.Connection
    Set myADORs = New ADODB.Recordset

    mySQL = "SELECT T1.Field1, T1.Field2, T1.Field3, T1.Field3, T1.Field4"
    mySQL = mySQL & " FROM Table1 T1"

    myADOConn.Open myConn
    myADORs.Open Source:=mySQL, ActiveConnection:=myADOConn
    If Not (myADORs.BOF Or myADORs.EOF) Then
        Do While Not myADORs.EOF
            ActiveCell.Offset(myLoop, 0).Value = myADORs.Fields(0).Value
            ActiveCell.Offset(myLoop, 1).Value = myADORs.Fields(1).Value
            ActiveCell.Offset(myLoop, 2).Value = myADORs.Fields(2).Value
            ActiveCell.Offset(myLoop, 3).Value = myADORs.Fields(3).Value
            ActiveCell.Offset(myLoop, 4).Value = myADORs.Fields(4).Value
            myLoop = myLoop + 1
        MsgBox ("No Records to Display")
    End If
End Sub



Similar Threads
Thread Thread Starter Forum Replies Last Post
db connection string Alexi ASP.NET 2.0 Basics 1 October 5th, 2006 05:25 AM
db connection dfeuerborn VB Databases Basics 1 August 19th, 2005 02:08 PM
db connection dfeuerborn Access VBA 1 August 17th, 2005 10:33 AM
DB Connection stu9820 ASP.NET 1.0 and 1.1 Basics 1 September 26th, 2004 09:01 PM
Preferred db connection mega Classic ASP Professional 3 July 2nd, 2004 08:39 AM

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