Using Access As Front End For SQL Server

Jul 18, 2012

I am a computer novice with no experience in PHP or SQL Servers in general. Is there anyway to set up Access as a front-end to an MS SQL Server without having coding knowledge? Is there any good wizard that will export data/formats to a SQL Server while still keeping the interface mechanisms of Access?

View Replies


ADVERTISEMENT

Access Front End Linked To SQL Server Database

May 24, 2006

Hi,

I created a database in Microsoft Access and then upsized it to SQL Server afterwards.

I now need to add a new column to a table in the database. So I added the new column in SQL Server using ALTER TABLE command. This column is a foreign key which has an associated parent key in another table which contains a description of the column.
e.g.
Main_Table
Case_ID Foreign_Key_Field_ID
1234 78

New_Foreign_Key_Table
Foreign_Key_Field_ID Foreign_Key_Field_Desc
78 Describes the ID 78


I also setup a CONSTRAINTREFERENCE between the fields in these 2 tables.

I then relinked my tables using the Linked Table Manager to pick up the change to the table I altered (Main_Table). I also linked to the new table I created (New_Foreign_Key_Table).

My problem:
In Access if I create a new table I can select the lookup value for a field in the Table Design view Lookup tab. However, I altered my table in SQL Server and then linked to it again to pick up the change. Now when I view the linked table in Access it does not have any lookup value AND it's not possible to change this for a linked table.

Is there some piece of SQL I can use in SQL Server to change the lookup value for the field in my table OR have I approached this the wrong way. Should I have modified the local table in Access and upsized it again?

Otherwise if I select all values from my Main_Table in a form it displays the Foreign_Key_Field_ID instead of the Foreign_Key_Field_Desc because the lookup value has not been set to Foreign_Key_Field_Desc

This posting is a bit long - I hope it makes sense.

Any ideas or suggestions would be greatly appreciated.

Many Thanks

View 1 Replies View Related

Tables :: Alter SQL Server Table From Front End?

Aug 1, 2014

I have an Access 2010 front end with linked tables via ODBC to a SQL Server back end. Is it possible to add, delete, and otherwise alter the tables on the SQL server from the Access front end? I've tried the following but received an error "cannot execute data definition statements on linked data sources"

DoCmd.RunSQL "alter table [dbo_tblAccountsMvOld] add column [cnt] byte;"

Is there code that will let me do this?

View 1 Replies View Related

Looking For A Front End For Access

Nov 28, 2007

Hello
I'm looking for a web based front end to put on my access database. Due to the way our work site is set up and the people who will be entering some of the data my boss wants it to be accessed through a web browser. Infopath wont let me enter data into an access database through a web browser and with DAPs becoming a thing of the past we dont really want to use them either. Some of the forms will be used for raw data collection while others will be used to fill out spill reports (i work in an environmental department) so we can get the information quickly. We have tried just using access to run everthing but have meet alot of resistance as most of the people who enter the data hate it. I also had someone enter half a spill report into an SQL sequence once..dont even know how he got there. That is the main reason we are looking for a web based data entry system. I dont believe my boss wants to use anything but access for the storage. So basicly we are just looking for a way for people to use a web browser to enter data into an access database. Any advice would be greatly appreciated. Thanks

View 2 Replies View Related

Using Access For SQL Front End

Oct 8, 2015

I want to use access 2007 as a front end for Sql Server DB. I want to create a basic input form, a search and display facility, and have some reports generated out of the application.

If this would be possible to do using Access in 20 days?

View 6 Replies View Related

Access DB With Excel Front End

May 23, 2005

i am creating a database that will house some supplier information. once designed, the db will be populated with all the data and then it will be used as a reference tool for future projects.

it was requested that an "excel front end" be used for lookup and ease of use to access the data when it is finished... i am not really sure how that works.

also, what is the best way to create contingent variables in access-- for example, if a certain value is in the database then you have a corresponding action. like if there were a field asking how many suppliers there were, and if say the number is 4, then 4 entry windows come up. a sort of if/then statement?

i hope this makes sense. thanks a whole lot

View 5 Replies View Related

Front End Table Access

Sep 7, 2005

I split my database so that users could only edit/view data through the forms but when I opened the front end the tables where still there (linked though) and I could edit and look at them. I assume this is normal so how can I prevent users from seeing the tables. Do I just set them to hidden? Thanks.

View 3 Replies View Related

Using Access As A Front End To MySQL

Sep 16, 2005

I have Microsoft Access on my desktop connecting to a MySQL database at my host, using an ODBC driver. I can read the data just fine. But when I try to add another record, it won't seem to do it.

Is it possible to update a MySQL database from MS Access on my desktop?

I have just one table. Very simple. Is it to do with primary key number generation or something?

Thanks,

Jon

View 1 Replies View Related

Access As Backend And Swishmax As Front End

Oct 7, 2005

HI:
anyone got any suggestions about using access 2003 tables as the backend and swishmax as the frontend. Thanks in advance.

View 2 Replies View Related

Sybase Backend Access Front End

Jan 12, 2006

Is it possible to do this?

if so what kind of connections would i use?

and how would i connect?

View 2 Replies View Related

Access Front End User Permissions

Dec 8, 2006

Hi,

I have an Access front end that has linked tables to a SQL Server back end database. The connection to the back end is a via a read only user because the front end is a reporting tool for querying, viewing and reporting on the data. I am able to view the data in the front end and set up new queries. However my user has the new query icon greyed out. I have not set up any kind of permissions or security levels to my knowledge so am a little mystified as to why their permissions should be different to mine! Does anyone have any ideas please?

View 1 Replies View Related

Increased Size Of Access Front End

Jan 25, 2008

I have been posting a lot recently and have greatly appreciated the help. I have just been looking at the size of a ms access project, this is a front end for another database so contains no data (runs as an mde for most). I have made a few modifications using access 2007 including at one point having it in the new file format, this was to fix a bug I was getting that caused a particular crash. I found converting it to 2007 and back cleared the problem.

Anyway the main things I added were an active directory query to enable certain buttons for a limited number of users. Including the VBA reference to do this. I also added a new report that when run in access 2007 (even in 2003 format) allows the new reportview so I can have an onclick event within the report for the user to gather more information which it wouldn't be appropriate to have on the main report.

This has balooned the size from 5mb to 50mb even after a compact and repair. Any suggestions as to possible causes could it really be adding vba references causing that much of an increase. I should say i'm not particularly worried it's ok for it to be that size I just wonder why such a massive difference.

View 2 Replies View Related

Design Changes In Ms Access 2003 Front End Db

Jan 19, 2005

We upgraded to ms access 2003. We used to be able to make design changes on just about anything in access 97 IF a user was not in the particular record/form/object. Now it seems we can't do much of anything in access 2003 unless in exclusive mode. (and we have many many users 24 hours a day) so it's even hard to import changes made off-line.

Is there anyway around this in 2003? Any other suggestions? Other than calling all users on the phone to tell them to log off (and some are logged in and not even there), can we 'boot' everyone off (although this really is not a good option for us either). This is a production database.

It would be good if we could just make quick changes as needed when we want as it was in the version 97.

Thanks

View 4 Replies View Related

Sybase Backend Access Front End

Jan 12, 2006

Is it possible to do this?

if so what kind of connections would i use?

and how would i connect?

View 1 Replies View Related

MS Access Front-end/SQL Back-end Connection Issues

Nov 9, 2006

I have an MS Access front-end with a SQL back-end database. It works great, I connect the MS Access front-end to the SQL back-end using a File DSN.

I have a few users (who are in the database the most) who get ODBC disconnection errors time to time. The cause of the error is not consistent, they seem to just appear out of no where. The table that reports the disconnection is not consistent either.

The only way for the users to resolve the errors is to exit the MS Access database, and then re-enter it.

I have had the DBA team analyze the SQL server, and it does not appear to be having any difficulties. We moved the database to another server (so it would be on a server with less traffic) and I rebuilt the File DSN. But the errors continue.

I will have the network team look into any errors, but I am not certain if they will find anything or not.

Has anyone run into this, and what resolutions did you come up with/work arounds did you perform? I am starting to consider upgrading to a Visual Basic .Net front-end, but wanted to see what options were left.

Thank you for your time.

T.J.

View 1 Replies View Related

Stop Access Deleteing 00 From Front Of Number ?

Aug 16, 2007

HI All,

Can anyone tell me is there a way to stop access automatically deleting 00's from the front of numebrs after they've been entered into a form.

I need to keep the 00's to ensure to different DB's match.

All help aprciated

-Elfman

View 2 Replies View Related

Front End = Access; Back End = MySql Problem

Mar 8, 2008

Hello

I´m working in an application using Access as FE and MySql as BE.

When I add some registers to this linked table, OK.

Problem: When I try to add a lot of registers (700.000!) it does´nt work and the msg is that the computer does´nt have memory resources to do the job.

If I try the same with Access tables it works fine.

MySql configuration, perhaps?

Thanks a lot

Renato

View 9 Replies View Related

Connect Access Front End To Mysql Back End

Dec 14, 2006

I have seen it done in the past but at the time my limited knowledge did not retain how to do it, it is connected through the odbc, im am running server 2000

what code do i use to connect and how do i set up the odbc

any help would be appreciated

Jabez

View 1 Replies View Related

General :: Run Access Front End As Public Service?

May 7, 2013

Simply put, I have a front end that does a bunch of file moving and if it encounters an error and stops processing (which is intentional) I am the only that can login to the machine and fix it.

Is there any way to run it that would allow others to login on their own user profile, solution the problem, and start the processing again or am I stuck to a "per profile" instance?

I can't just let others login as me

View 2 Replies View Related

General :: When Converting Front End To MDE Access Hangs

May 4, 2013

When converting the Front End of my database to an MDE, Access just hangs and says (Not Responding). I've left it our an hour and there's no sign of stirring.The Back End converts in just a second or so.

View 5 Replies View Related

General :: Cannot Open Front End Access File

Jun 28, 2012

I was successfully using a front end and back end setup for the past two years. Today I needed to push through some changes and "deactivate" the previous back end database and setup a new back end database. The new back end database has been sent; however the front end is not recognizing the back end database anymore. I need to re-link the existing front end to the back end database - I cannot import the front end into a "fresh" file because I put a password on the VBA Editor.

View 14 Replies View Related

How To Create A Standalone Front End In Access 2013

Dec 18, 2013

So I have finished a database and split it which is great. Only issue now is how do I turn the front end into an executable so no access to any parts of the access program or system can happen? I have done this before in a very old version of access but how to do it in Access 2013.

View 2 Replies View Related

SQL Back End / Access Front End - How To Secure Data

Oct 2, 2014

How to secure SQL data when my MS Access database must access it using linked tables. Generally speaking on the Internet, assertions that migrating to SQL is more "secure" are rampant; explanations of just how that is are scarce.

Sure I can restrict access and put different roles on different tables, then put different people in those roles, but that doesn't really do me any good. Ultimately I want to achieve the following: Allow them to do all kinds of stuff (including Update, Delete, Insert) using my access application, but I would NOT want them to have those same rights if they installed SQL Server Management Studio, or for that matter, simply created an ODBC connection using another access or excel application.

In other words, ideally, I'd like to have SQL server view my Access application as a user in its own right. Not sure if that type of impersonation or identity is possible to achieve.

View 4 Replies View Related

General :: Create A Front End Application For Access Database

Apr 10, 2013

I was looking to see if there was an easy way to create a user front end desktop application for a small access database.I have three users on different computers i would like them to input or amend data within one table of the access database.

View 2 Replies View Related

General :: Front End Support For MS Access Login Form?

Aug 3, 2015

I have created a Login form in Access Database in which the users can log in to enter the work information like how much time and which job they worked on particular day , this data is stored in a table . How can make the users to use this form , I dont want to access to be installed on every machine..I want to give something like a URL so that the users can use it..

which software I can use for creation of website so that it can be compatible with ms access ...I basically need to create a front end application and connect ms access my project in the back end scenario..

View 6 Replies View Related

Access FE SQL Server BE

Dec 5, 2007

We're currently looking at changing a couple of my more complex Access 2003 databases to Access Front End with a SQL Server Back End. This should give us better stability and reduce network traffic loads. I was looking at the Access help file on Upsizing and came across the following paragraph. On face value to a novice like me this would seem the easy option - but is it?

"Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application.

This approach requires very little application modification since the code is still using the Jet database engine. "

Would anyone recommend this approach?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved