Post reply

Connection Access 2003 .adp

  • May 22, 2006 at 3:44 am

    #67436

    Hi

    I am using the following string as part of a password routine to connect an Access 2003 .adp to a SQL Server 2000 database.  The SQL Server is called 'Genoa', the security is regular Windows login.

    Dim ConnectStr As String, srvstr As String, Datasource As String

        Dim srv As Object

        Set srv = CreateObject("SQLDMO.SQLServer")

        srv.Name = Datasource

            srvstr = "Genoa"

            srv.LoginSecure = True

            srv.connect srvstr

            ConnectStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Transactions;" + _

                        "Data Source=" + srvstr

       

        CurrentProject.OpenConnection ConnectStr

       

        SysCmd acSysCmdClearStatus

    It works excellently on my machine.  However, on a test machine it returns the error message below at the 'Set srv = CreateObject....' point:

    'ActiveX component can't create object.'

    Has anybody any ideas what the problem might be?

    Thanks

    Paul

  • May 22, 2006 at 12:06 pm

    #639341

    Sounds like you don't have the SQLDMO library (dll file) on your computer.   Mine is here:

    C:\Program Files\Microsoft SQL Server\80\Tools\binn\SQLDMO.DLL

    Within Access you can check by opening a module, then clicking Tools, References, and scrolling down until you find (or don't find)  "Microsoft SQLDMO Object Library".

    By the way, I would say it's better practice to make an explicit reference in your project, by selecting the library from that References dialog, and then you don't have constructions like this:

        CreateObject("SQLDMO.SQLServer")

    There are environments where deferred resolution of object types is a good thing, but I doubt that working in Access you would run into any such need.

    If you go ahead and make the explicit reference (once you get the SQLDMO library installed), your first lines of code becomes this:

        Dim srv As SQLDMO.SQLServer

        Set srv = New SQLDMO.SQLServer

    Using an explicit reference (through the references dialog) allows you to make an explicit declaration and assignment (as above) to the object type.  This is generally considered a good thing, though, as they say, "actual mileage may vary".  You have to decide whether deferred resolution (evil) or explicit reference/declaration (benign) is right for you.  As you can see, I scrupulously refrain from editorializing, mostly.

  • May 23, 2006 at 3:19 am

    #639437

    Hi Charles

    Thanks for the excellent reply.  Yes, I am sure this is the problem.

    I'm not quite sure how best to best rectify this.  I am working on Windows 2000 server with SQL Server installed.  The SQLDMO file exists in the same directory as your given path.  However, the distributed Access 2003 .adp front ends are on machines running XP and without SQL Server installed - or the path to the file.

    Should I run a batchfile to copy the file and make the directory path also?

    Many thanks

    Paul

  • May 23, 2006 at 5:53 am

    #639464

    As long as the library is installed, you should be able to go into a module, then select Tools, then References, locate the SQLDMO library in the popup dialog box, select it, then close the References dialog and use the code in my first reply.

  • May 23, 2006 at 6:05 am

    #639467

    Hi Charles

    I probably didn't explain myself properly - or I am misunderstanding what you are saying to me.  In References on the XP machines, the Microsoft SQLDMO reference doesn't exist at all.  A search shows the file doesn't exist on these machines at all.

    Does this mean I need to copy the SQLDMO.dll file to a location on the XP machines?

    Thanks, Charles

    Paul

  • May 23, 2006 at 6:39 am

    #639479

    Paul,

    Try Coping the file SQLDMO.dll to C:\windows\system32

    Then you need to register the file:

    Start Run      Regsvr32 c:\windows\system32\sqldmo.dll

    It should then appear in the list of references.

    Richard

     

  • May 23, 2006 at 7:30 am

    #639494

    Thanks Richard

    I can now use a batch file to install the Access front end and put the sqldmo.dll file in system32.  However the batch file terminates at this point.

    What happens when I Start and Run (or run from Command)  is it asks for an application to open the sqldmo.dll file.  I need it to install itself onto each computer - if that is what needs to happen.

    Many thanks

    Paul

  • May 23, 2006 at 7:52 am

    #639503

    Paul,

    Regsvr32 registers the .dll file on the computer and makes it available in the references section of the Access file.

    However - you wont have SQL on every machine.  Sorry if that didn't help.

    Richard

     

  • May 23, 2006 at 7:57 am

    #639505

    Thanks Richard

    I'm surprised it doesn't work somehow, as I culled the original code above from Northwind.adp

    Paul

  • June 8, 2006 at 9:39 am

    #642575

    I'm back on this I'm afraid.  I need to install sqldmo.dll and related files - so I can use SQL Objects.  I've been searching the Internet and trying for hours to register the file on XP Client machines - but not go.

    I have put the following files in the locations below:

    sqldmo.dll     Program Files\Microsoft SQL Server\80\Tool\Binn

    sqldmo.rll     Program Files\Microsoft SQL Server\80\Tool\Binn\Resources\1033

    sqlresld.dll    Program Files\Microsoft SQL Server\80\Tool\Binn

    sqlsvc.dll      Program Files\Microsoft SQL Server\80\Tool\Binn

    sqlsvc.rll       Program Files\Microsoft SQL

    Server\80\Tool\Binn\Resources\1033

    sqlunirl.dll     System32

    w95scm.dll    Program Files\Microsoft SQL Server\80\Tool\Binn

    I have also tried putting the files in System32 with the appropriate subdirectories - still not go.  I cannot seem to get this to work.

    If anybody has successfully installed sqldmo.dll for use on SQL Server 2000 client machines running XP,  I'd be really grateful for a few pointers.

    Thanks

    Paul

  • June 9, 2006 at 4:54 am

    #642738

    Hi

    Success.

    With a bit of fiddling, I've managed to do it with the help of:

    http://support.microsoft.com/kb/q248241/#appliesto

    It works excellently.  A good way to distribute without having to install SQL Server Client on each machine.

    Paul

  • June 12, 2006 at 6:27 am

    #643053

    Thanks for the follow up, will save someone else a headache someday.

    Andy
    Connect with me on LinkedIn

  • June 12, 2006 at 7:52 am

    #643072

    Hi Andy

    Sure.  Posts on this excellent site are very helpful all round.

    However, tempering the success - SQLDMO loads on some of our XP clients - but not on others.  I'm now struggling to work out why.  If you have any ideas, please let me know.

    Paul

     

  • June 12, 2006 at 9:49 am

    #643154

    What error are you getting?

     

    Andy
    Connect with me on LinkedIn

  • June 13, 2006 at 2:29 am

    #643301

    OK, all sorted.

    The probem with the XP clients which didn't work was to re-install SP2???  Don't know why - but it worked........

    Best wishes

    Paul

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply