Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: 2nd sql backend connection for report rs

    I have an sql2000 backend with a file dsn connection within my AccessXP front-end. Everything working great. For one small place in code, I need to lookup some records in a second remote sql2000 to which I have permissions. I have the second remote sql2000 in SQL Enterprise Manager and can query within Query Analyzer. I've tried to go by the white paper from the msdn site, but I've run into trouble. Maybe you can help. I want to basically create a simple report that I can attach to an automated email as a snapshot. Been doing that with reports from the regular access.mdb connection, but needed this separate connection to another remote database.

    Here's what I've tried, but it gives me the Run-time error of '2593' feature not available in an mdb and it stops on the me.recorset=rs line. So, any ideas about what I can do to get the recordset into the report? It's not a project file, just an mdb.

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strADOCon As String
    strADOCon = "Provider=Microsoft.Access.OLEDB.10.0;Data
    Provider=sqloledb;Data Source=tada;Initial Catalog=tada;User
    Id=tada;Password=tada"
    Set cn = New ADODB.Connection
    cn.Open strADOCon
    Set rs = New ADODB.Recordset
    With rs
    Set .ActiveConnection = cn
    .Source = "Select event_name as [Name] from tada.dbo.tblEvent where
    event_pk=15063"
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
    End With
    Set Me.Recordset = rs
    Set rs = Nothing
    Set cn = Nothing
    cn.Close

    jb

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Connection to another SQL Server

    Would creating a System DSN and then linking the tables into the MSAccess application via the System DSN be an option?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    Paul,

    Geez, don't know. How would I do that? Would that conflict with the file dsn I have set up for the general back-end sql2000 database (which works fine by the way)?

    Janet

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    System DSN

    You can create a system DSN via Settings, Control Panel, Administrative Tools, Data Sources (ODBC), and select the System DSN tab - New.

    Or in MSAccess, you can select File, Get External Data, Link Tables, change the File Type as: to ODBC and then select the Machine Data Soruce, New button. Select SQL Server as the Driver, enter a name, and select the SQL Server. Click Next, Next, and change the default database to the one you want from SQL Server. Click Finish and then Click the Test button (if everything is ok with permissions, the test should be ok). The new System name will show, click OK with that DSN name highlighted and you should then see the tables from the default database you specified above. The important thing with System DSNs is that you HAVE to make sure that the DSN names on all the computers are EXACTLY the same otherwise you'll have problems. When you link in the table, make sure you select the "Save Password" checkbox! The table will link into MSAccess as dbo_TableName. You can rename this but if you re-link in the table it will link in again as dbo_TableName (we use linked tables in all our MSAccess applications so people can design their own queries and we also design our vba code in MSAccess to utilize the dbo_TableName convention.)

    I'm not sure if it will conflict with your File DSN or not. It's been a while since I used File DSN's. Attached is program which will create the System DSN on other machines once you have established the System DSN. All you need to do in the attached MSAccess app is link in the tables as stated above (delete any that might already be in it), then make this available to other users. When they run it on their machine, it will automatically create the System DSN on their machine using the same DSN name based on what tables you've linked in the mdb (if you have linked tables from several different DSNs, it will create a System DSN for each of the different tables).

    Hope this helps.
    Attached Files Attached Files
    Last edited by pkstormy; 01-06-06 at 18:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •