SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ACCDB file using SQL Server Backend


ACCDB file using SQL Server Backend

Author
Message
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 479
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.
Attachments
accesserror1.jpg (10 views, 23.00 KB)
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 479
I figured it out. I needed to add the accdb file as an import --> Link from the external data ribbon.
WendellB
WendellB
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1753
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!
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 479
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search