Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    4

    Access 2007 to SQL

    Once again...Newbee (I think I will always be a newbee)



    We have SQL server on a NON-DOMAIN server. We are trying to access it (sorry for the pun) from DOMAIN members computers.
    I managed to create an ODBC link to the server (using SQL authentication ) and was able to "push" the database to the SQL server, as opposed to using the SSMA on the server itself. But, I didn't split it first. Should I have?
    When I look at the Access Database, I see all the objects, including the tables. I really want the application portion as a separate entity to be copied to other computers, or better yet, on a server share so only one copy would be out there for the users to use.
    QUESTION: Is there a way to split it now, and then connect the application portion to the SQL database?
    QUESTION: Am I beating my head against the wall trying to work outside, yet within, the DOMAIN? We really do not want this setup as part of the DOMAIN for internal political reasons (outsourced IT).
    I have a ton of other questions, but this will do it for now.
    Thanks

  2. #2
    Rawb is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Quote Originally Posted by thisboyneedshelp View Post
    I managed to create an ODBC link to the server (using SQL authentication ) and was able to "push" the database to the SQL server, as opposed to using the SSMA on the server itself. But, I didn't split it first. Should I have?
    Unfortunately, I don't have a lot of experience with linking Access to a Non-Access DB (or any experience, really). My understanding though, is that in those cases you have to use ADODB to link to the Tables via VBA. Since I'm interested in doing this kind of thing for a database I'm working on right now, I'll look into it.

    I really want the application portion as a separate entity to be copied to other computers, or better yet, on a server share so only one copy would be out there for the users to use.
    The best solution here is to have a separate copy on each computer. To increase security, I'd recommend only installing the Runtime on the client computers and, if possible, distributing an ACCDE instead of an ACCDB.

    If you have them all using a single database on a networked/shared drive, you increase the risk of corruption/locking issues.

    You can get the best of both worlds by storing a copy of the database somewhere on the network and setting up a VB Script/BATCH file to regularly update the user's computers. That way, you only have to worry about keeping the network copy up-to-date.

    QUESTION: Is there a way to split it now, and then connect the application portion to the SQL database?
    Like I said above, I think that the linking will be done in VBA. If that's the case, then your VBA code will take care of this part for you.

    To answer your question directly though, yes, you will be linking to the Tables on SQL server as if it were your Backend Database. It's just that you won't be able to use the Linked Table Manager to set it up (I think).

    QUESTION: Am I beating my head against the wall trying to work outside, yet within, the DOMAIN?
    Absolutely not! When dealing with Databases, I find it best to keep them separate from the Domain structure (unless you need it for authentication). And even if you do want to use the Domain for authentication purposes, you can do so from "outside" the Domain by accessing the AD Server using LDAP.

  3. #3
    Join Date
    Mar 2013
    Posts
    4
    Well, I am making great strides. One of the issues I was having was not running the proper version of the migration tool. Eureka! When I used the 32 bit version, it performed very well and I was able to copy the application to DOMAIN computers and shares and it works well. The only issue I am having now is:
    We have the SQL Server doing the authentication and not windows. I just think that would be better for now, but I may back up and play with the Windows thing. But, no matter what I do (relinking tables, setting up a DSN, etc., etc.) it ALWAYS attempts to connect with Windows authentication, not SQL. The interesting thing is that I have one DOMMAIN machine out of the three that logs right in. I just can't seem to find out the difference.
    All in all I am pleased so far and will keep digging.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-04-2013, 05:52 PM
  2. Export Table in Access 2007 to Multiple Workbooks in Excel 2007
    By hutchinsm in forum Import/Export Data
    Replies: 5
    Last Post: 03-01-2012, 05:23 PM
  3. Ms Access 2007 report export to excel 2007
    By Stan2man in forum Access
    Replies: 6
    Last Post: 11-23-2011, 01:24 PM
  4. Replies: 2
    Last Post: 06-18-2011, 09:55 AM
  5. Replies: 0
    Last Post: 11-17-2009, 02:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums