Access Front End/SQL Backend


I've been looking through all the previous posts and it seems that I am doing something wrong. I have two databases, one front end that had all the forms, reports, modules etc. and a backend that had all the tables. I converted the backend tables to MS SQL 2000 and linked them to the front end by ODBC no problem. I am using DAO for a lot of updating. and a lot of drop down boxes. I have no problem adding new records to the various tables, however as soon as I start to try to modify a record I get a write conflict error and I'm the only one using it. What is interesting is that the first button, the 'Save Record' is greyed out and I cant use it. I'm pulling my hair out, because I can't figure out what I'm doing wrong or what I'm missing when I create the tables in SQL. So, I hope there is someone who has had the same issue (there are no problems, jut unresolved issues) and can point me in the right direction. Thank you


Sponsored Links:



I'm a newb - so go easy.

I have a SQL 2000 back end database which runs the business day to day activities (Sales, Dispatch etc).
I have a requirement to update 10,000 prices in the system. I can't do this through the GUI as it will take too long drilling into each product 1 by 1. I need a tool which will allow me to dump out all the data I need into a data grid (excel look and feel), let me update it and then allow me to save the updates directly back into SQL as they are updated through the datagrid.

I can do this with access if I link to a single table, however, my problem is, the data I need to update is located in several different tables within SQL. The only way to get this information out, is query the database like this:

select sku,desc,price,suppliersku,shipmethod from products
inner join price on products.sku = price.sku
left outer join suppliersku on products.sku = suppliersku.sku and price.supplier = suppliersku.supplier

My idea is to use access with 3 linked tables. Use a query to export this to a temporary "access" table, make my updates and then save this back into the relavant tables in the SQL database.

My questions are:
Can this be done?
Is this the most efficient way of doing this?
Can I not just interact with the table directly?
How do I do it?
Can I use excel to achieve the same results?

I'm using access 2007 and I'm no expert - if you can give me a simple step by step example of how to do this, I'd really appreciate it.
My issue is not with pulling out the data, but saving this back into the SQL backend.

If anyone can send me an example of how this is done, I'd be really greatful.

Also, if you think you can knock this up quickly and easily and would like the job of doing so, let me know and I will pay for this work to be done. I have much more work we're crying out for at the moment. it's all access front end with sql backend for many users.




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.




I've got Access Tables that have reached the 2GB limit and I want to do a SQL backend. I've exported my Access table to SQL and linked it up. However, it take 20 times longer to run a query that populates or updates data in SQL than in Access. Anyone have suggestions as to how I can improve this???




This seems easy but I am having a problem Have not touched this in a few months
Have the front end MS Acccess DB using an odbc link to the backend MS SQL server SQL Server has both tables and views
Production works fine
I want to take a copy of the Front End and point it at a different backend MS SQL server which contains the same tables and views (Call this my DEV Server)
So within MS Access you use the utility Linked Table Manager and repoint all the tables and views to the new server by the ODBC Connection
Everything seems to go fine but I cannot update the tables afterward in the DEV Server
What did I miss in the process?

RRII




Hello all,

I am working with a Access file that has linked tables (previously local tables that have since been upsized) that reside on SQL Server.

Each user gets their own copy of the Access front-end.

If I'm not using DAO and I'm just using built-in form functionality for updating and inserting etc..., how do I implement record-locking? I'm guessing this must be done at the SQL Server level, but I'm not sure.

Could anyone shed some light on this issue for me?




Hi there,

I am hoping to have this Access 2003 front-end linked up to a SQL server which stores the back-end table.

What I am wondering and I really hope it works this way is.......if users can access the standard query grid through Access front-end with the tables now stored on the back-end on the server.

Will users still be able to use the query grid to do query as they normally would in Access 2003's query design view?

I am new in this arena, and am hoping that I can get some input from experts.

Thank you




Hi Folks,

In building a access front-end that uses linked SQL Server 2008 tables, for testing and development purpose I used integrated security with ODBC DSN and then linked the tables I needed to the front-end selecting primary keys on the tables that required it for data entry to be possible.

I am at the point where I would like to roll this application out, but I don't want to create ODBC DSN instances on the machines and don't want to delete and re-create the links to the tables with the appropriate username and password for each data entry user (different security for different users).

Is it easily possible to somehow, someway link these few SQL tables with appropriate primary keys without having to create a ODBC DSN and with the username and password actually stored in code SQL security NOT trusted/integrated security.

An example would be the following:

SQL Server IP address: 155.155.155.155
SQL Database: ELEPHANT
SQL Table: TIGER
Username: John
Password: Citizen

If there is an easy way to do this, I would really appreciate any help with this! Would something like a DoCmd.TransferDatabase acLink work? I suppose depending on the method, I may need to drop and then re-create the link each time, but that shouldn't be a problem. A function I can load in a Autoexec would rock if possible. Some of the tables require primary keys, which I haven't a clue how to create in code.

Thank you very much for any help you can provide!!

Joe




Hi,

I just recently migrated my Access 2003 database over to MS SQL Server 2005 using the Migration assistant provided by MS. However at my business we have multiple workstations using this and when I try to use the database (through the access front-end) we get an error message saying that there was an error connecting to the server. error code was 18452. please help...

jlabre01




I am currently maintaining a bridge inspection program that I created for all the County Engineers in the State of Ohio, that they use to submit bridge inspection and inventory data on a yearly basis to the Ohio Dept. of Transportation. The DB was originally a split DB, but now I am working on a new version that will be remotely accessed by the users as a MS Access front end with the data sitting on an SQL Express server. I have successfully transported the data (which exists in two tables - "dboBR86 and dboBR87" to the SQL Express server, and connected the SQL tables via ODBC to the front end. In the front end, when the app is launched, the user will put in a password, clicks OK, the password is associated with the correct criteria value from the password user table, and passes the criteria value to a login conformation form. The user then clicks the LOGIN button and the criteria value is then passed to the two queries BR86 and BR87 that are the Record Sources for the BR86 and BR87 forms. The problem I am having is with the BR87, because it is treated differently in the program than the BR86 data. When changes are made to any record in the BR87, only those records get marked for export. When the record is changed and you try to exit the form, the message:

Run-time Error '3254' ODBC--cannot lock all records.

appears. Both tables have a Key field, the Access record options are set to Open Mode: Shared - Record Locking: No Locks. The BR86 and BR87 forms have no record locking either.

The VBA code is as follows. The line in red is where the program halts:

Public Function UpdateCard(ByVal sSFN As String, ByVal sCard As String)
Dim rsBR87 As Recordset, iBegin As Integer, sSQL As String

sCard = UCase$(sCard)
sSQL = "SELECT TRAN_CODE FROM BR87 WHERE SFN = '" & sSFN & "'"
Set rsBR87 = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbDenyWrite, dbOptimistic)
With rsBR87
.Edit
If IsNull(!TRAN_CODE) Then !TRAN_CODE = Space(15) 'Need to watch these Nulls--see my documentation!
Select Case sCard
Case "A"
iBegin = 1
Case "B"
iBegin = 2
Case "C"
iBegin = 3
Case "D"
iBegin = 4
Case "E"
iBegin = 5
Case "F"
iBegin = 6
Case "G"
iBegin = 7
Case "H"
iBegin = 8
Case "I"
iBegin = 9
Case "J"
iBegin = 10
Case "K"
iBegin = 11
Case "L"
iBegin = 12
Case "M"
iBegin = 13
Case "N"
iBegin = 14
Case "O"
iBegin = 15
End Select

If iBegin = 1 Then
!TRAN_CODE = "C" & Mid$(!TRAN_CODE, 2)
ElseIf iBegin = 15 Then
!TRAN_CODE = Mid$(!TRAN_CODE, 1, 14) & "C"
Else
!TRAN_CODE = Mid$(!TRAN_CODE, 1, iBegin - 1) & "C" & Mid$(!TRAN_CODE, iBegin + 1)
End If
.Update
End With



End Function

Any help would greatly be appreciated.
bsmiller92




I have "upsized" an Access back end database to SQL Server. The database contained some fields which were type Hyperlink and Autonumber in Access, and their data types were re-assigned during the SDL Server translation. We need to continue using the Access front end to enter data into the database for the near term. How do I modify the Access front end so that I can both enter Hyperlink data paths and resolve links to the converted files in the SQL Server database?




Hi guys,
I have developed an application in access which I upsized to a SQL Server 2000 Database but now I would like to move the database to another server but I don't have any idea where the connection details are stored in the Access Front End. Do you guys have any idea where that is normally stored when you upsize the Access Database.

Thanks




Dear Access Expert,

My client has asked my why I am endorsing Access as the Front End for his application. He thinks that VB.Net is better.

I have never programmed in VB.Net and I know Access is considered a RAD tool. From this forumn I have read that it can cut development $ and time by up to 66%.

Also from this forumn I have learned that Access Front end has some limitations when it comes to the look of the GUI. Basically we are limitted to the controls shipped with access or from third party dealers. Access cannot be web based.

I have also learned that VB.Net is more secure than the Access Front End which I do not fully understand.

I am confident that MS Access is the right solution for this client as the application is for a small business (6 concurrent users), it doesn't need to be web-based, will use LogmeIn for remote access and has to be under $10K.


Please note that I don't want to discuss the backend (Jet vs SQL Server) just the front end (Access vs VB.Net.) in this thread.

How do I convince the client that MS Access is the right platform despite all the media and hype for VB.net?

Thank you for your feedback.

Peter.




I have recently migrated from access to SQL server 2005 using the specific migration tool downloaded from MS. Our orgnisation has at most 1-5 concurrent users and the database is relatively small in size (8M after compact).Every user keeps a local copy of the Access GUI running querys and forms while the actrual data is holding on a back end server sitting in another room.

The problems we met are:

1 Search(Ctrl-F) on "company" names and "people" takes 1-5 mins (untick "the search fields as formatted" box SOMETIMES makes the search instant)
This problem seems to impact on random user. e.g. users sitting in the same room may have different performance at the same time and the situation may reverse the day after. Reopen the Access front end or restarting the SQL server somestimes solve this problem but sometimes not.
All queries other than search looks fine like opening large tables and filtering.

2 A particular tables stoped me from inserting under form view (no [Star]button allowed for insert in the navigation bar) but you can insert by unhiding the underlying table and insert directly.No serurity settings applied on that table.All form settings checked. All the other tables works fine.

3 Some linked table occasionally freeze and prevent people from editing while it all looks OK in the last second.I have to manualy delete and relink the table to make it work again. linked table manager didn't help this time.

anyone has any ideas ??????




I am experimenting with an Access Front End and an SQL Server Back End with Linked Tables. I set up SQL Server 2005 Express on my desktop PC and used SSMA to transfer the tables and some queries to SQL Server. The tables are linked to the Access 2000 Front End. Everything was working fine but when I close the Access .mdb Front End file, I sometimes cannot reopen the Access File. The Access Lock File.9.ldb stays open and does not close when I have closed the Access File. I am unable to reopen the Access .mdb file. I cannot delete the .mdb file or the .ldb file. I get a message that I "Cannot delete" "There has been a sharing violation. Source or destination file may be in use." I can reboot the computer and then reopen the file and it will then close correctly.
Does anyone know what I can do to correct this problem?

Bill D




so i have this string:


	Code:
	               
                          strSQL = "INSERT INTO tbl_school " & _
                                   "(school_name, school_degree, school_major, school_startdate, school_enddate) SELECT
(school_name, school_degree, " _
                                 & "school_major, school_startdate, school_enddate) FROM tmptbl_school;"
                          .Execute strSQL, , adCmdText + adExecuteNoRecords
                          strSQL = "SELECT Last_Insert_ID();"
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastSchoolID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With

the thing is tmptbl_school is wihtin the ms access front end. so it keeps telling me that the table does not exist which makes a lot of sense, does anyone know how to correct my syntax?




Hello,

I am trying to develop a front end that will call on a stored procedure.

I have a form setup where the user types a value in a textbox and then clicks "Search" (command button)

In theory, I am trying to have it call on a stored procedure to then populate the other fileds on the form.

The isssue I am running into is that the stored procedure prompts like a query asking for the input value to then search for the record. I am looking for a way to assign the value of the textbox to a variable which will then populate the prompt of the stored procedure to find the record.

Since this is an ADP, I have the database bound to the form in the Control Source field of the form, so when the stored procedure runs, the other values bound to a column will populate.

If there is someone who can give me a solution to link the textbox's value to the stored procedure prompt, I would appreciate it.

Thanks,

Sledge




Hi,

We are using sharepoint as our backend, and using Access (2007) for our front end.

In sharepoint I have certain fields in our lists hidden to users who view via the browser. The access front end is for the admins, and more of the fields are viewable using Access.

What is a good method to adding a password on the Front End to stop anyone from using it, to view the other data?

Thanks,




Hi Folks,

Just wondering what are the major differences between creating a front-end in access compared to a front-end with Visual Basic, .NET, and ASP on the web. It seems to me that many folks are fixated on .NET and ASP and don't realize that MS Access is very powerful and is capable of doing almost anything a .NET application can do. So I am a bit confused and wondering what are the real and true advantages/comparisons in using VB, .NET, or ASP over access for a front end when creating a moderate complexity database application/data-entry app that perhaps links several SQL Server tables together in SQL on the back-end. To me, access is much easier to grasp than the other possible mediums/solutions, maybe I am partial to it because it is cheap and fast/easy ... am I wrong? Also, with creating an app in VB or .NET it seems that the app would have to be tested on different OSs and systems and it can be very difficult to get all that to work together whereas in Access most of the times those issues do not exist. Also, is there any way anyone knows of, and I haven't found one yet, in which you can take access front-end and convert it to say .NET?

Thanks,

Joe




I have two Access front ends for SQL Server 2000 written a million years ago. They have been running on Server 2000 Terminal Server with an Access 2000 runtime for TS. I now need to move them to Windows 2008 and SQL 2008. I moved them both to Windows 7 64bit with a minimum of difficulty. When I moved them to Server 2008, I got the same error messages. The fixes that worked in 7 didn't work in 2008. Here are my problems:

Environments:
Windows 7 64bit
SQL 2008 SP1
Access 2003 RT or Access 2003 (Access 2010 blows up)

Windows 2008 R2 64bit
SQL 2008 on separate server
Access 2003 RT

Front end 1 gets an ODBC error. A separate copy gets a you do not have permissions to access error. Saw both of these errors on 7 but some minor changes on SQL Server made them go

Front end 2 just hangs completely on 2008. On 7, it gives me the following error. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. It does open and appear to work after I hit OK.

My best theory is that there's something in the front end I need to edit. However, if I open the front end, all I get is the application and nothing that will let me alter the forms. I also tried the hold down shift and open that I saw elsewhere on the site. Same thing. Any help woudl be appreciated.