Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ACCDB file using SQL Server Backend Expand / Collapse
Author
Message
Posted Monday, April 2, 2012 10:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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.


  Post Attachments 
accesserror1.jpg (4 views, 23.60 KB)
Post #1276728
Posted Monday, April 2, 2012 11:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
I figured it out. I needed to add the accdb file as an import --> Link from the external data ribbon.
Post #1276780
Posted Monday, April 2, 2012 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:16 PM
Points: 146, Visits: 537
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!
Post #1276783
Posted Monday, April 2, 2012 12:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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.
Post #1276833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse