Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    49

    Unanswered: microsoft access 2007

    hi

    i'm new to ms access 2007, and I would like to know what this "Link table manager" does which can be found under database tools?

    I'm migrating ms access 2003 to ms access 2007 and, i found out last time that if I click on the "Link Table Manager" and select all tables to be updated & then click on "machine data source" and select the database which I'm working with under "data source name" and click ok, then I'm getting a message that all tables are link.

    After which the connection of my database is very very fast. But if I don't do this, when I opened any forms in the database it takes a minute or two before it open. So I'm not sure what does this "link table manager" do and as well as the database I created under "data source name". Does it really solve the connection problem? confused


    Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Linking tables allows you to have a 2nd mdb (or SQL Server) 'backend' so to speak (which you do by: File->Get External Data->Link Tables). When you link a table into an mdb, you will see a <- or globe for the icon. This means that the table doesn't actually exist in the mdb (ie. you could delete the table in the mdb but it would still exist in the backend.)

    The Link Table Manager is the control center so to speak for managing tables that are linked into an mdb. You use it to refresh tables linked into your mdb. If for example, you moved the backend mdb file which holds the actual data tables, you can click the 'prompt for location' in the Link Table Manager and then tell it where the new source is for your backend mdb tables. Or if you made design changes to your backend mdb which has the tables, you'd open the Link Table Manager, click Select All (or the appropriate tables), and then OK to refresh the tables.

    Linking tables from another mdb file is 'splitting' an mdb file (ie. you have a backend which holds your data tables, and you have a frontend which has the linked tables and all your forms, queries, code etc...). It's the ideal route to go since you can then easily make changes to your frontend mdb file without having to worry about copy data to/from your working mdb file. You then simply put out a new frontend for the users to open.

    If your backend tables are SQL Server, you would establish a Data Source Name (ie. ODBC DSN). This is the connection (or as I like to call it, gateway) to establish the connection between your mdb and the tables on SQL Server.
    Last edited by pkstormy; 11-30-09 at 22:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2009
    Posts
    49
    @pkstormy : thanks for explaining. What I mean here is the Link Table Manager in Access 2007. Is it the same? I was looking for the : File > Get External Data > Link Tables in Access 2007 but I couldn't find it. Perhaps its in the Ms access 2003. Is that correct?, are you describing here the Link Tables in ms access 2003?

    What does linking tables do with the speed, after I convert the database from ms access 2003 to ms access 2007?

    Normally the database works fine in ms access 2003. But after I convert it to ms access 2007, it becomes very slow, when I opened forms, tables, queries, very slow.

    Then, what I did, I go to the "Database Tools" in ms access 2007, then clicked on "Linked Table Manager" and selected the "Always prompt for new location" and then create a new data source. please see the attached .png. and if you have further question please let me know

    thanks
    Attached Thumbnails Attached Thumbnails selectdatasource.png  
    Last edited by tintincute; 12-01-09 at 04:12. Reason: add .png file

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. Sorry - I was describing MSAccess 2003. 2007 moved everything around and I believe it's under the External Data tab. As soon as I learn where the $@!! MS moved everything around to in MSAccess 2007, I can hopefully help better.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Feb 2009
    Posts
    49
    @pkstormy: that's nice of you. by the way i forgot to attached the file yesterday. Here is what I mean:

    http://www.freeimagehosting.net/imag...6eaadc428c.png

    thanks

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The picture message in your link typically means that you do not have privelages to create/edit ODBC DSN's on the specified computer (or 'connection'). That could be due to many reasons and you may need to contact the network administrator (or whoever controls permissions with the connection to your data source server.)

    It could also be an erraneous MSAccess 2007 error if it actually let you create a new ODBC DSN but I'm wondering 'where' your ODBC DSN is getting created once you click OK and you say it works. It may not be allowing you to create the ODBC DSN on a specified connection (ie. Citrix/Terminal Server connection) but may also be letting you create it on your specific computer - hard to say. You may want to (on the source computer or citrix/terminal server connection), go to Control Panel - Administrative Tools -> Data Sources (ODBC) and see if the actual ODBC is created. It may be letting you create the ODBC DSN on your computer (which may be different than your 'connection' to the server) and denying any attempts if it's via a connection (again such as Citrix). For example, I cannot create ODBC DSN's when I connect into our UW trhough our Citrix connection but I CAN create ODBC DSN's on my local work computer.

    You typically need an ODBC DSN on each computer (or connection) that's connecting to the linked tables.

    But once the ODBC DSN is created, you should be able to 'Refresh' the linked tables using the Linked Table Manager. Again, check Control Panel and Data Sources to see if the actual ODBC DSN exists (or you can also do a regedit.)
    Last edited by pkstormy; 12-02-09 at 20:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Feb 2009
    Posts
    49
    @pkstormy: i did what you said and yes i saw the different databases I created under "User DSN" > "USER DATA SOURCES" > "NAME" ...please see attached .png file

    Does it have something to do that I'm using my local computer and not the real server. I mean the back-end which is the SQL Server is local.
    And when I edit the ribbons in microsoft access 2007, i changed the connection string into (local) instead of using the server name

    You think that's where the problem lies?

    Thanks
    Attached Thumbnails Attached Thumbnails odbc.png  

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

    This might be out of my range of expertise since it's 2007 and I'm still learning how Microsoft changed things with 2007, especially anything that might be ODBC involved (although it should be the same). But I'm confused on a few things you've mentioned and I'm perhaps assuming some of the things you mentioned are a typical setup.......

    I'm not quite sure what you mean by "And when I edit the ribbons in microsoft access 2007, i changed the connection string into (local) instead of using the server name"

    but here's the best advice I can give you based on what I've experienced:

    whenever you open an mdb file which has backend SQL Server (via linked tables), when the mdb opens, MSAccess looks at the ODBC DSN for the connection parameters (for how you, as the user is connecting) and determines how to connect (note that a USER DSN is different than a SYSTEM DSN and you may want to research this.) For example, if I log into my UW network system (remotely via citrix), and an ODBC DSN hasn't been configured on Citrix itself, I'll get an error and won't be able to connect and see the data. BUT, if I were to open that same mdb file on my local computer at work (where I CAN create and have that ODBC DSN), everything works fine. This is assuming the SQL Server is on a server OS, not a LOCAL computer!!

    I can't say for sure if your problem exists with a User versus System DSN or something else (I just don't know enough about 2007 and your setup). It seems as though the problem your experiencing is based on how you're connecting to the system (network) and how you have SQL Server setup.

    What's confusing is this: "Does it have something to do that I'm using my local computer and not the real server."

    and this: "I mean the back-end which is the SQL Server is local."

    Please note this - (since SQL Server is typically not a 'local' installation unless YOU and ONLY YOU, the user for that computer want to access it, but instead SQL Server is typically installed on a server OS in order for other users to connect to it.) - ie. if you were to install SQL Server on a local computer that is running XP (not a server OS), you CANNOT have external users connect to that SQL Server. It MUST be on a SERVER operating system!!!

    Again for example, if I install SQL Server on my local desktop running XP and want other users to connect to it (regardless of how I configure the ODBC DSN), it just ain't going to happen!!

    Since I don't know your setup, I'm sorry but I don't quite know how to help you other than perhaps start troubleshooting with different users and connections to see where the problem lays.
    Last edited by pkstormy; 12-03-09 at 22:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2009
    Posts
    6

    Problems with Forms "Controls Bound to Expressions"

    Hi all,
    I am working on Tables that were copied from an existing databse and relabeled to expand the program;
    For example: TableDalas was copied and relabled TableDalas_New; and FormDalas_New was also made from FormDalas.
    My problem is redirecting the properties of FormDalas_New so that any information entered will populate TableDalas_New.
    I have changed the Record Sources in the propertied of the forms but still get an error message that reads
    Control can't be edited; it's bound to the expression '[Site_list].[Column](1) each time I try to enter data in a field.

    I work mainly with the properties window but can follow basic instruction on coding. Please see codes on one Form below:
    ----------------
    Option Compare Database

    Private Sub Form_Load()

    Dim i As Integer

    For i = 0 To Me.Controls.Count - 1
    If Right(Me.Controls(i).Name, 4) = "list" Then
    Me.Controls(i).Width = 0
    Me.Controls(i).Height = 0
    Me.Controls(i).Locked = True
    ElseIf Right(Me.Controls(i).Name, 3) = "txt" Then
    Me.Controls(i).Locked = True
    End If
    Next i

    End Sub

    Private Sub Form_Close()
    Dim i As Integer

    For i = 0 To Me.Controls.Count - 1
    If Right(Me.Controls(i).Name, 4) = "list" Then
    Me.Controls(i).Width = 0.125
    Me.Controls(i).Height = 0.125
    Me.Controls(i).Locked = False
    ElseIf Right(Me.Controls(i).Name, 3) = "txt" Then
    Me.Controls(i).Locked = False
    End If
    Next i
    End Sub

    Private Sub NextPage_cmd_Click()
    Dim whereclause As String

    whereclause = " dbo.Rotavirus.caseid = '" & Me.Caseid_txt.Value & "'"

    DoCmd.OpenForm "Rotavirus 2 - Case Report", acNormal, , whereclause
    DoCmd.Close acForm, Me.Name

    End Sub
    Private Sub PrevPage_cmd_Click()
    MsgBox "There is no previous page. You are on page one.", vbOKOnly, "No previous page"
    End Sub

    Private Sub Site_list_BeforeUpdate(Cancel As Integer)

    End Sub

    Private Sub Site_txt_BeforeUpdate(Cancel As Integer)

    End Sub

    Private Sub Switch_cmd_Click()

    DoCmd.OpenForm "Main Switchboard", acNormal
    DoCmd.Close acForm, Me.Name

    End Sub

    Private Sub Switch_cmd_Exit(Cancel As Integer)

    End Sub
    --------------
    Thanks- idi

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please start a new thread for a new question.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2009
    Posts
    49
    @pkstormy:

    sorry if I confused you. When doing the migration its all done locally. So for example: a .mdb database if I open it directly, in my test PC, it will said SQL Server login failed. To avoid this, what we did, we have this tool developed by someone where we can change the connection string. I attached also a .png where you can see it how I did it.
    After I change the server name into (local) I can then open the database without asking the login name from the SQL server.
    I think the reason we did this so that we are not working on the productive site. After that I did the conversion from 2003 to 2007 in ms access.
    And then, after I converted then I can start changing the ribbons and work with the database.
    But then, I have to link the tables so that the database will be faster. But if I don't do that, the tables and forms will open so slow. That's why I'm not so sure what does it do, while doing this linking tables to make it faster.
    Attached Thumbnails Attached Thumbnails accessform.png  

Posting Permissions

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