Results 1 to 15 of 15
  1. #1
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52

    Connection String for MS Access Database incorrect.

    Hi

    I am trying to extrapolate data from an MS Access 2007/2010 Database.

    I have the following code in VBA but the connection string is incorrect. I have added the relevant REFERENCES libraries

    Code:
    Private Sub btnGetMsAccessData_Click()
    
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    Dim sSQL As String
    
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\MyNetworkPath\BP-MasterDashboard Source\BP_Planning_by_PT_dept_be.accdb;Mode=Read"
    
    Set oConn = New ADODB.Connection        ' Open a connection.
    oConn.Open
    
    
    sSQL = "SELECT * FROM Tbl_Start_Leaver"        ' Make a query over the connection.
    Set oRs = New ADODB.Recordset
    oRs.Open sSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText
    
    
    MsgBox oRs.RecordCount
    
    
    ' Close the connection.
    oConn.Close
    Set oConn = Nothing
    
    
    End Sub
    It fails saying Unknown Application error on the oConn.Open line.

    I have tried to link a Worksbook to one of the tables and this works fine.


    I then looked at the "Connection" and copied it into my code but still no joy.

    Any ideas would be appreciated.

    Thanks in advance.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't need ANY code to do this.
    link the table and make the query, it shows the count.

    access query will not get the syntax wrong.
    zero code to write.
    faster production.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Where is this code? Are you running your code in an Access file or another program/application?

    I would start by making sure you have made the correct Active X reference in your References. IF you do not have the correct reference, that line should not compile. Are you able to compile your code?

    Having said that, I rarely use ADO, if ever. So I am not extremely knowledgeable. However, you are going to have additional issues. For instance, the .Open method will need an argument (maybe use the connection string). Also, your recordset is asking for adLockBatchOptimistic and I am not positive this is supported. I do believe it will be a conflict with the parameter in your connection string, though (mode= read).

  4. #4
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Sorry maybe I did not explain properly.

    The code above sits inside an Excel File and I am trying to extract the data from the Access DB.

    I am not sure what you mean by link the query. ?

    Linked worksheet data locks the Access Db and the users can not get in then. (it creates a .laccdb file for the Back End Tables file).

    Thanks

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is this code in Access or Excel?

    Also you assign a value to sconn, but never use it

  6. #6
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    The code in Excel.

    I was trying to replicate :
    https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx

    I am a little confused now as you are correct I can see where sConn is used.

  7. #7
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Ok so I am adamant I want to get this working.

    I am now trying the following and get the same issue.

    Public Sub test2()
    Dim oRs As ADODB.Recordset
    Dim sConn As String
    Dim sSQL As String


    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\myNetwork\BP-MasterDashboard Source\BP_Planning_by_PT_dept_be.accdb;Mode=Read"

    sSQL = "SELECT * FROM Tbl_Start_Leaver"

    Set oRs = New ADODB.Recordset ' Create and Open the Recordset object.
    oRs.CursorLocation = adUseClient
    oRs.Open sSQL, sConn, adOpenStatic, adLockBatchOptimistic, adCmdText

    MsgBox oRs.RecordCount

    oRs.MarshalOptions = adMarshalModifiedOnly

    Set oRs.ActiveConnection = Nothing ' Disconnect the Recordset.
    oRs.Close
    Set oRs = Nothing
    End Sub

    the sConn string is used when opening the oRs

    Still getting
    Automation error
    Unspecified error

    How difficult can this be.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Were you able to make the correct Active X reference? Step one in this link provides a screenshot of what I am talking about.
    http://analysistabs.com/excel-vba/ad...ting-database/

    Edit: I made a mistake earlier and corrected my reference the SQL...
    As for the connection string and the .Open. You are assigning your connection string to sConn
    Code:
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\MyNetworkPath\BP-MasterDashboard Source\BP_Planning_by_PT_dept_be.accdb;Mode=Read"
    I think you need to use this on the .open of the ADO connection (oConn.Open sConn)
    / Edit

    Instead of adLockBatchOptimistic maybe you can use adLockReadOnly
    https://msdn.microsoft.com/en-us/lib...v=bts.10).aspx

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mond007 View Post
    ...
    the sConn string is used when opening the oRs

    Still getting
    Automation error
    Unspecified error
    ...
    OK, I see the connection string argument being used, now. What about the Reference to Active X? Take a look at my post #8 regarding the reference thing. Also in post 8, take a look at the adLockBatchOptimistic comment. I need to attend to a few things, but will check back in on this thread later.

  10. #10
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Yes, I think I have the correct references.

    Click image for larger version. 

Name:	accessref.jpg 
Views:	18 
Size:	92.1 KB 
ID:	26105

    I have grabbed even a simple database from sample databases and can not even connect to them.

    I hope I have the correct librarys

    Thanks in advance.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I mocked up a test and used your code from post #7. I was able to get it to work, no problem. The only difference is that I referenced Microsoft ActiveX Data Objects 2.8 Library. I did not try the 6.0 version. I went straight to the latest of version 2.




    Code:
     Dim oRs As ADODB.Recordset
     Dim sConn As String
     Dim sSQL As String
    
     sConn = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\ServerName\TestADO\MyDatabase.accdb;Mode=Read"
     sSQL = "SELECT * FROM tblEquip"
     Set oRs = New ADODB.Recordset ' Create and Open the Recordset object.
     oRs.CursorLocation = adUseClient
     oRs.Open sSQL, sConn, adOpenStatic, adLockBatchOptimistic, adCmdText
     MsgBox oRs.RecordCount
     oRs.MarshalOptions = adMarshalModifiedOnly
     Set oRs.ActiveConnection = Nothing ' Disconnect the Recordset.
     oRs.Close
     Set oRs = Nothing

  12. #12
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi

    I do not recall seeing "Microsoft ActiveX Data Objects 2.8 Library" in my list of references I will have another look in the morning.

    Thank you very much. I think this seems to be the root cause of the problem. I will get back to you.



  13. #13
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Sorry this still does not work.

    I found the "Microsoft ActiveX Data Objects 2.8 Library" and added it but still get the error.

    Click image for larger version. 

Name:	runtime rror.jpg 
Views:	17 
Size:	27.2 KB 
ID:	26120

    How hard can this be.... annoying. Losing the will to live now.

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Edit: did you remove the reference to the 6.0? /edit

    I can assure you these things can be very difficult. That is why you should strip away as many of the dynamics as possible and test in a Control Environment. Once you have isolated the one thing that you are after (connecting via ADO), slowly move your way towards a production environment by adding additional elements (like connecting over a network).

    Are you testing this in a temp folder on your C drive? I would start by creating a new Access file and a new Excel file in a temp folder on your C drive. Add a simple table in your Access file and create a simple query in your Access file to retrieve the data. Test that query and use that SQL in your future VBA.

    When you create the VBA module in your Excel file, make sure you create it for your Worksheet. Start off with your Button Control and test it by making sure it fires a msgbox. Make sure you save your Excel file as Macro Enabled. After saving your newly created VBA, the Excel file should have the xlsm file extension and not the xlsx file extension.

    If after adding your VBA to connect to the Excel file, a connection is not successful, you can start to look into Trusted Locations and make sure permissions Trusted Locations are allowing the two files to talk to each other.

  15. #15
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi

    Managed to get this working when taken down to base principles.

    Thanks ever so much for the advise it worked a treat.

    I can add each step and see where it breaks from here on in.

    Thanks once again. : - )

    I can debug and add each step from here on in.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-03-2015, 10:02 AM
  2. Remote Desktop Connection Broker connection string
    By Philosophaie in forum Access
    Replies: 1
    Last Post: 09-14-2015, 03:51 PM
  3. Specifiying a connection string in ms access forms
    By thebionicredneck2003 in forum Access
    Replies: 4
    Last Post: 05-09-2013, 04:19 PM
  4. Create dynamic connection string to Access
    By janwane in forum Access
    Replies: 2
    Last Post: 10-07-2011, 06:56 AM
  5. Replies: 1
    Last Post: 07-08-2009, 03:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums