Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Cannot see list of SQL tables when linking (Access 2003 SP2)

    I have created a data source name, I have upsized an Access database. I have gone into SQL Server and the new database with all it's tables are there.

    When i try to link froma FE Access database to the data source name, it does not show the list of tables i have just upsized?

    What do i have to do to see this ODBC list in Access when i try to link via ODBC?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Hi Pat,
    This is a new one to me.
    Double check that you do indeed have sufficient permissions. The bare minimum to see the tables is read [select] and if you have created the tables in SSMS Express you certainly should have ample rights.

    My only "real" guess is that you are trying to use "Linked Table Manager" (<Tools><Database Utilities><Linked Table Manager>) and not "Get External Data" (<File><Get External Data><Link Tables> then choose <ODBC Databases ()> from the Files of Types dropdown at the bottom of the Link dialog box). But unless you already have a linked table (or more) you would get an error trying to use "Linked Table Manager" since there would be no tables to link to.

    The following link pertains to Linked Table Manager kb835519. But this is even less than a shot in the dark.
    I'll keep looking, but have found nothing that exactly relates to your problem.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Hi Gary, when i want to link new tables i select tables/New which then provides a way of selecting the data source name after choosing ODBC as the file type.

    What i will do today is to delete all databases from the WILMASQLExpress SQLServer list of databases.
    I will then delete the data source names i have created.
    I will reboot (maynot matter)
    I will create the Data Source Name
    I will then use the upsizing wizard in Access to create a database in SQLServer
    I will check that all the tables are present in that created database in SQLServer
    I will then try to link to those tables from and Access FE that has none of those tables.

    Wish me luck as you wave me .....

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Still no good, i cannot see the SQL tables in the ODBC link screen.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cannot see list of SQL tables when linking (Ac

    Hi Pat

    What AV are you using? I have heard that Norton 2007 can play havoc with this <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>
    Jerry

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Cannot see list of SQL tables when linking (Ac

    I am using NOD32, I wouldn't touch Norton's with a barge pole.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    I got it, i hadn't nominated a value in the field "Change the default database to"

    Sorry about that, hope i haven't wasted too much of your time, you too Jezza.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Hi Pat,
    Please bear with me, this is rather long winded.
    I've setup a virtual XP machine/Office 2003 and SQL Server 2005 Express to try and mimic your setup.
    This is what I tried. If the options you are using are different let me know, but when I just tried it the tables "automatically linked themselves", but I encountered a problem as well.
    My steps were (from within the database I am upsizing):
    <Tools><Database Utilities><Upsizing Wizard>
    I chose the Create new database option. <Next>
    Entered WinXPOff03SQLExpress for my SQL Server choice
    Checked Use Trusted Connection and left the default name in the "What do you want to name your new SQL Server database box",
    which was the name of my Access database with SQL added on the end (TestDBSQL). <Next>
    I selected all the tables to be Exported to SQL Server. <Next>
    Left the default choices in the next screen and as to timstamp fields I chose "Yes, let wizard decide." <Next>
    Chose the option Link SQL Server tables to existing application. <Next>
    Then clicked Finish
    The wizard then created the database, linked my tables and renamed the original, local tables by appending "_local".
    However......I cannot find the ODBC data source name in the ODBC Data Source Administrator and therefore will not be able to find any new tables I would have made in the new SQL Server database because I have nothing to "Get external data" from.

    I do have a disclosure to make.....
    I do not like the Upsizing wizard, (because of other reasons and now this one) and always import the data into SQL Server. This is the long way, but I can use multiple data sources and I know where the DSN's are and what they are named as well as making sure the data is data-typed the way it should be. (As an aside: I also ensure that every table has a Primary Key and a timestamp field at this time). The creation of the ODBC data source is next, pointing to the new SQL Server database using the ODBC Data Source Administrator. I then copy my original Access database and save it somewhere safe and secure (remember the mantra.....backups are good, backups are good). Then in the original Access db, I delete all the tables, go to <File><Get External Data><Link Tables> then choose <ODBC Databases ()> from the Files of Types dropdown at the bottom of the Link dialog box, find the correct data source and click <OK>. In the Link Tables dialog box, choose the tables you want to link (you'll notice they all are prefaced with "dbo.") and again, click <OK>. The linked tables then appear in the Tables object window, but they are prefaced with "dbo_" so you must rename them. (I've got a simple little piece of code which renames the tables to their original names.) You're ready to go at this point. I do all of my table design within SSMS from this point on and to link to a new table go through the steps in this last paragraph. To refresh the table links (which you'll need to do if you change the design of an existing table) go to "Linked Table Manager" (<Tools><Database Utilities><Linked Table Manager>).

    So if you would please, try it this way and let me know how you get along.

    fyi here's the renaming code:
    <pre>Public Sub RenameSQLTables()
    Dim obj As AccessObject
    For Each obj In CurrentData.AllTables
    If Left(obj.Name, 4) = "dbo_" Then
    DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
    End If
    Next obj
    Set obj = Nothing
    End Sub
    </pre>

    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Sorry again Gary, if you see my previous post you will see what a dummy i have been. And you spent so much time composing this post.

    The renaming code came in handy. thank you.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot see list of SQL tables when linking (Access 2003 SP2)

    Hi Pat,
    Glad you got it. Don't worry about the time. It's Sunday night here and since I'm not much of a TV watcher it was a good diversion. I really do not use the Upsizing wizard at all, so it was good practice.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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