DSN Less Connection Code

Posted on 2006-05-24
Last Modified: 2010-05-01
I am trying to execute this code from a module in MS Access 2003:

Option Compare Database

Public Sub DSNLESS()
Dim m_rdoConn As rdoConnection
Dim m_rdoErr As rdoError
Dim m_rdoResult As rdoResultset
Dim m_rdoEnv As rdoEnvironment
Dim m_rdoCol As rdoColumn

Set m_rdoEnv = rdoEnvironments(0)
Set m_rdoConn = m_rdoEnv.OpenConnection(dsname:="", _
                prompt:=rdDriverComplete, _
                Connect:="uid=Hawk_User;pwd=Hawk33y3;driver={SQL Server};" _
                & "server=MSSQLCLPROD01;database=HAW;")

End Sub

I am receiving a message: "Runtime error 429 - ActiveX Component can not create object".

What am I missing?
Question by:Kirkeous
    LVL 65

    Accepted Solution

    dont know if u suffer from the design time licence problem

    LVL 11

    Expert Comment

    If you're using rdo, you need to have mdac 2.5 or below installed.  Why would you want to develop with this considering it is now obselete????

    You should switch to ado and install a later version of mdac.

    For your reference:
    LVL 11

    Expert Comment

    <If you're using rdo, you need to have mdac 2.5 or below installed>

    Forget that part, I wrote it thinking of something else.
    LVL 17

    Expert Comment

    Microsoft recommend using a UDL file to test your connection.

    Basically create a blanmk text file rename it like MyData.udl, then double click on the file and the UDL Wizzard kicks in.

    Once you have selected a Provider, MS Acces for example, then complete the rest of the details.  You get a Test Connection button so you can prove you can open your database without have to write one line of code, or even think.

    Once you have a UDL file you can open it and copy and paste the connection string into your application.

    One cute thing is to leave the UDL file in place and open it with code. (Warning its is a unicode file so you may need to convert from unicode.)  Then using instr you can find the connection string. This means you can relocate your data without changing/recompiling code.

    If you are using SQL Server or security enabled databases, another trick is to place a token where the password is like $$PASSWORD$$ you can replace the token withing the string for the correct password.

    So now you have a connection string you know will work you can start to code.

    As suggested above you need to swap to use ADO.  But as ADO is about 2000% slower and less reliable than DAO many people are staying with DAO.

    Author Comment

    Yes, we did have license issue. Now it is fixed and I can use ADO so I am good. Thanks for your help! though!
    LVL 65

    Expert Comment

    Kirkeous, u said u had a license issue. My initial post mentioned that possibility. Did it help u or not?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now