Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Join Access to SQL Server tables (A2000+)

    I am developing a front end to a large SQL server database, but am having difficulty in creating table joins that involve Access Jet tables and SQL Server tables. When I create sample tables locally the joins work fine, but in the production environment, there appears to be some sort of mismatch that means I get no result rows in the joined result.

    I suspect the problem relates to the way null values are mapped, or maybe to differences between text fields and the SQL Server equivalents.

    Can anyone provide me with some pointers to things to look for in this situation, please?

    Thanks.
    Geoffrey

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Join Access to SQL Server tables (A2000+)

    With a large SQL Server database, you shouldn't be creating joins to Jet tables because that pushes the whole thing into Jet, which is not designed to handle large recordsets efficiently. Perhaps if you explain why you're doing what you're doing, someone can give you some useful suggestions.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Join Access to SQL Server tables (A2000+)

    Thanks Charlotte.

    The front end is used to allow the user to select, via a series of forms, the data which will be extracted from the SQL Server (backend). However, the selection forms themselves need to be populated by enquiries to the backend to determine, for example, which fields have been collected for any given sample period. In general, these preliminary queries are accessing only small index tables, so although I realise it may be preferable to use pass-through queries for everything, I confess that I was taking the easy way out. I create queries on the fly, based on earlier user responses, and use these to set the RowSource for combo boxes, etc. (It is also because I have not used pass-through queries much, and so am not really confident about using them - at least partly because of the differences I understand that are required in some aspects of the sql). Presumably the alternative is to run the query in the backend to create a local table that could be used as a rowsource (?)

    The final result of the user activity is a query that does indeed get sent back to the backend to do the 'real' data extraction, but my front end is failing because some of the joins (to the backend) are not returning any rows.

    Geoffrey

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Join Access to SQL Server tables (A2000+)

    There isn't any real reason to put small tables in Jet instead of SQL Server (unless you aren't allowed to add tables to the SQL Server database at all). Be that as it may, in general you should be able to do joins between Access and SQL tables. Even text fields should work as long as the SQL tables aren't using nvarchar fields. But in general you are safer using the numeric field types, generally the long type in Access and the int type in SQL Server. I wouldn't worry about using pass-through queries at this juncture - they can be used to enhance performance if necessary, but in general ODBC does a pretty good job of optimizing queries as long as all the tables are in SQL Server.
    Wendell

Posting Permissions

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