April 2, 2012 at 10:10 am
I have an access front end to a SQL Server 2008 backend. I developed the database and front end on my desktop first. The database works as planned. I then created the same database on a sql server that is on the network for other users to use. I also installed the SQL Server 10.0 Native client on the users computers who would be running the accdb file.
When they run the accdb file they can only read the data, and they are constantly being prompted to login to the database. When they are prompted to login the server name is my computer hostname. Also when they choose the drop down lists they do not see the foreign key data the should appear.
I also tried going to the External Data ribbon -Choosing ODBC database - It then asks me to export tablename to tablename - I click OK - Create a DSN - Login with the username/password I created - and then receive the attached error message.
I don't believe this is the right approach as I am not trying to create a new table or export the database.
How am I supposed to change the prompting issue so when they launch the accdb file they dont have to constantly change the server name and get prompted so frequenly?
What could possibly cause the drop down lists to show now data, even though it works locally for me?
Is the fact that they can only read the data related to the first two issues?
Thanks. I appreaciate the help.
April 2, 2012 at 11:19 am
I figured it out. I needed to add the accdb file as an import --> Link from the external data ribbon.
April 2, 2012 at 11:21 am
What version of Access are you using (2007 or 2010)? Also what sort of network are you connected to? The easiest way to connect to a database is by using Integrated Security, but that generally means you need to have an NT domain running where the network logins are administered from a domain controller server. Also, you probably need to define each user with SQL Server security and establish the appropriate permissions for each user. That should be done using SQL Server Management Studio unless you are very familiar with the system sprocs. That is the likely explanation for only being able to read tables, and not seeing the drop-down lists. If you are using a single login, then you can't tell who does what to what.
The usual approach is to create an ODBC data source on each workstation that uses the database. That is done using the utility under Administrative Tools in Control panel, and chosing the database and server you are connecting to. The tool under External Data that you are attempting to use is an upsizing tool that takes an Access table and upsizes it to SQL Server. If you are using Windows7 workstations, you want to do it as a User DSN, not a System DSN, and the SQL Server driver in Windows7 is actually a bit newer than the Native 10 client. If workstations are on XP or Vista, then you do want the Native 10 client installed.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 2, 2012 at 12:15 pm
Wendel,
thanks I was able to create the odbc in the user dsn. I was trying to create it via the export part of external data but I realized i needed to do so through the import odbc. Then I was able to choose the tables that are part of the database and now I am able to update the records. Thanks for your input.
I'll have to look into integrated security though. For now I just set up a single user account as my team is only 3 users.
September 23, 2017 at 1:49 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply