Access front end linking to sql server backend Results

All,
I am considering using Access 2010 Front End linked to SQL Server Backend to Build a User Interface for Data Entry. I have a couple questions.

1. Can you publish an Access Database that is linked to a SQL Server backend to Sharepoint?
2. When a User pulls up the Published Access form on Sharepoint do they have to have MS Access installed on their computer?

Thanks
Brian

Hi everyone! I'm back!

In the past I asked about setting up an Access front-end with a SQL Server backend, and the replies I got were quite satisfactory (pbaldy's was one of them.

A few days before I tendered my resignation, my boss had given me an assignment to create a system for a financial services company (it would be the second project related to money-lending I have ever handled).

Now, the client's requirement is to have its data centrally stored in a database located on a server in the client's main office, and all branches located in different provinces will enter transaction data into that database using some interface - which I thought of at first would be a VB. The branches and the main office computers will be linked via a VPN.

But because I know that the development of a VB interface (either an ASP.NET Web interface or Windows application) takes so long, I'm considering using Microsoft Access 2007 as my development tool for the interface instead. It will use linked tables that access the SQL Server over a VPN.

Is this setup even possible? I only have limited amount of time to finish this project. Have any of you guys embarked on a similar project? I really need your response.

Details:
Backhaul speed is 2 mbps.
Client speed is 384 to 1 mbps.
Number of clients is 8 computers.


Thanks a lot!

Jan

What happened to the stored queries defined in the FE of our Access FE-BE application, after upsizing the Access BE (just tables) to SQL Server?

As an example: [ProjectChances].[PropNum] ç related è [Sales].[PropNum]

Below the excerpt of a SQL Server printout showing the relationship between two tables:

ALTER TABLE [dbo].[ProjectChances] ADD
CONSTRAINT [ProjectChances_FK00] FOREIGN KEY
(
[PropNum]
) REFERENCES [dbo].[Sales] (
[PropNum]
)
GO

An original query using this 2 tables before upsizing:
DELETE Right([Prop],1) AS Expr1, [ProjectChances].*
FROM [Sales] INNER JOIN [ProjectChances] ON Sales].[ PropNum] = [Sales].[ PropNum]
WHERE (((Right([Prop],1))

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

Hello, I wondered if someone could help.

I have a simple access front end and a SQL 2000 backend db. I have linked the tables using ODBC.

If I run the a simple "select * from mytable" query ( mytable has 65,000 rows, 19 fields) the data is returned but the total number of rows on the record navigator bar at the bottom of the query window does not populate even if I leave it for hours. And SQL reports that the select statement is still runing and takes out a lock on the table.

If I click on the last record arrow button on the navigator bar, the query completes and SQL releases the lock.

This problem with the statement not completing, is causing the dreaded ODBC Call Failed error, when the user tries to then edit a record whilst this select query is running. (as you would expect)

So I wondered if anyone knows why odbc linked tables behave in this way, because if I import the same table to Access it behaves as normal, with the select statement completing quickly as expected.

I have tried the following so far:

Different versions of Access (97, 2003 - I found the the problem did not occur if using 2003 project)

Updating ODBC drivers

Select just one field from the table (same problem)

Reduce the number of rows (this fixes the problem, but for obvious reasons isn't a solution)

thanks in advance.

I have an application built in Access 2002 and running in Access 2002 /2003. It is a split with an Access front end and multiple versions of the back end. Access, Sql Server 2005 Express, and Sql Serve 2008 Express. All of the versions in what ever combination run fine with the application running Access 2002 or 2003 on the client side and running Access 2002/2003 or either version of SQL Server Express backend on the server side. The tables are linked so that the application remains an Access application except for storing the data. There is a desire to upgrade to Access 2007/2007 runtime.The application runs fine with the Access 2007/2007 runtime when the front end iis Access and the back end is Access but fails when SQL2005 or SQL 2009 is the back end. The application has code that errors out when the client is Access 2007 or Access 2007 runtime and the backend is SS 2005/2008 with an ODBC error - ODBC call failed - unable to convert string to date - error #241.
The code fails in both of the following statements (a DCount and a Docmd.RunSQL).
1:
....
strSearch = "[CustomerId] = " & Chr(34) & Me![cboCustomerId] & Chr(34) _
& " and [CustomerType] = " & Chr(34) & Me![cboCustomerType] & Chr(34) _
& " and [OrderDate] = " & "#" & Format(Me![cboOrderDate], "MM/DD/YYYY") & "#" _
& " and [OrderNumber] = " & Me![cboOrderNumber]

intCnt = DCount("[CustomerId]", "tblCertificate", strSearch)
If intCnt = 0 Then
.....
2:
.....
strSQL = "Insert Into tblCertificate (CertificateNbr,CustomerId,CustomerType,OrderDate, OrderNumber," _
& "PONumber,Method) Values(" & intNextCertificateNbr & ",'" & Me.CustomerId & "','" & Me.CustomerType & "'," _
& "#" & Format(Me![OrderDate], "MM/DD/YYYY") & "#" & "," & Me.OrderNumber & "," & Me.PONumber & ",'" & strMethod & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
...
I know that SQL Server uses a different syntax for its dates ( # vs ' and " vs ' ). I wonder why the the application works on the Access 2002/2003 client side for both an Access function and a RunSql command but fails when the Access 2007/2007 runtime client is used. Could it be the ODBC driver that is being used?
Any ideas?
thanks..

Hi,

I have a Access 2003 application that include two Access files(one for frontend and one for backend).it works fine.

I just move the backend Access file to SQL Server 2005.now I use Access 2003 front end to access lined tables with DAO and the linked tables are linked to SQL Server 2005 with ODBC.but I found it is very slow to access SQL Server 2005 database with Access 2003 front end..

Please let me know how to make it a little faster to access SQL Server with Access 2003.

Thanks for help

Mark

I am moving my first database over from Access to SQL Server 2008.

I exported all my existing Access data using the supplied utility to the server so the backend is now Leave.mdf

My question is how do I link up Access as the front end as when I go to link data it doesn't recognise the .mdf file, i've tried upsizing and cannot get it to find an OBDC data source.

I am using Access 2000 - is there any quick fixes for this problem

Help!!

I am new to the SQL databases and have been working on getting a few of our database applications converted to SQL

Currently I have a database in which i upsized to SQL from Access. The backend data went over fine, and the front end linked up nicely using the linked manager.

However, the front end is currently on our shared file server so that other users can access the front end. (this worked great when the backend was also Access)

Now, when users open the new frontend linked to SQL, the data is not displayed and the linked tables don't seem to be working.

I'm wondering if this has something to do with the File Data Source being created on my machine. ???

If that is the case, would i need to install the file datasource somehow on other machines? What is the best way to distribute a front end within our network when it is linked to an SQL Server??

I have been told in the past that it's best to have a local copy of the front end on every computer that uses it... would there be any issues doing this when it's an SQL backend??

Any help would be greatly appreciated, I think my biggest problem is that i don't know where to start and the sites i have found that do talk about it are pretty advanced for my limited understanding.

Hi Guys. I have what I think is a simple enough question but an hour of searching hasnt come up with a direct answer. I think it is "No" but wanted to make sure.

We have an old program in VB6 written years ago that I have been keeping a float for the past several years. It has always be a VB6 front end that hits a Access (JET) database via DAO.

I would like to get the project running on a SQL Server database as the backend and avoid using Access as the middle man via Linked Tables. In other words, I was hoping to update the VB code to be able to connect directly to SQL Server but NOT have to rewrite all of the DAO code that rely on editing the database tables via Recordsets.

But, I keep running into the error that the Recordsets are not updatable when I try to do a rst.edit. Knowing that DAO was mostly designed to work with JET, I assume that it CANNOT edit via a direct connection to SQL Server?

Here is some code:


	Code:
	dbsSQL As DAO.Database
Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, ODBC;Driver={SQL Server};" & _
   Server={FSSQLSERVER_1};UID=SQL_Editor;PWD=Edit_SQL;)

Dim rstTEMP As DAO.Recordset
Set rstTEMP = dbsSQL .OpenRecordset(strSQLQ, dbOpenDynaset, dbSeeChanges)
rstTEMP.Edit '= BIG FAT ERROR
rstTEMP("CUR_ID") = int_tdms_loc_id
rstTEMP.Update



Hi

my access front end database wont communicate with the sql backend, once i have opened a form

also, i cannot create a linked table to the sql server

i select link table form the 'new table' dialogue box then ODBC databases form the file type drop down, and nothing the dialogue box closes and thats that

i must have a some sort of connection problem that has suddenly occurred, but i am lost to fnd out why

any ideas appreciated

I have an access database frontend with a SQL Server backend, the backend is located in Kansas City and the frontend is on a users desktop in Cali. It takes forever for the server to run some simple querys, is there a better way to do this so it doesnt take so long?

What i think the problem is that i have linked the tables and put query in the access front end, so when the user wants to query it has pull that entire table to the users and then run the query on it, is there a way to make access push the query to the server and have the server run it then give the results back?

Hi everyone,

I have several Access application(all split with Front End and Back End databases) that I want to upsize to SQL Server.

All tables in these databases have an Autonumber field as the primary key. What I want to do in SQL server is use GUIDS for the PK. Is there any easy way to link upsized tables to each other using GUIDS as PK and FK instead of the Autonumber PK and related FK.

I obviously can do it by writing scripts to do this albeit somewhat tedious to do :-(

A second question - sorry guys and gals. How do I create referencial integrity in SQL Server like you do in Access?

As you may have guessed I am new to the wonders of SQL Server.

I am in the process of moving all my backend databases from Access to SQL Server. I have upsized all the tables but having problems with my Access Front End. I have several forms based on queries that when run, pull information from three tables. When I had the tables in Access, after running the query I could make changes/updates to the query results.

But now that I have upsized the tables to SQL, I can only view the data. I can't add, delete or make any changes. All my tables have a key field in them.

If I run a query on just one table, then I can make changes/updates.

Can anyone help?

My access front-end uses VAL function to sort the out put so that the non-numeric values are ignored in the sort. My list displayed with the above data set will look as follows...

10
10D
20
21B
32
38B
100
112
213
381

We have recently migrated our backend to SQL Server. Since then this query stopped working since VAL is not a recognized function in SQL. Since I can not use SQL specific function in Access query, I have written the following stored procedure to get the work done.

CREATE PROCEDURE sp_GetTestList
AS
BEGIN
-- Sorts the Test list appropriately.
-- Without this sort the list would display 10, 100, 10D, 112, 20, 213, 21B, 32, 381, 38B
-- This proc would return the above Test list as 10, 10D, 20, 21B, 32, 38B, 100, 112, 213, 381

SELECT tblTest.Test
FROM tblTest (NOLOCK)
ORDER BY
CAST (
CASE SIGN (PATINDEX ('%[0-9]%', tblTest.Test))
WHEN 0 THEN NULL
ELSE
CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
WHEN 0 THEN tblTest.Test
ELSE LEFT (tblTest.Test, PATINDEX ('%[^0-9]%', tblTest.Test) -1)
END
END
AS Numeric),
CASE SIGN (PATINDEX ('%[^0-9]%', tblTest.Test))
WHEN 0 THEN NULL
ELSE RIGHT (tblTest.Test, LEN (tblTest.Test) - (PATINDEX ('%[^0-9]%', tblTest.Test)) + 1)
END
END
GO

I have linked this proc to be used as a pass through query in access. It works as desired for sorting. But now I lost the ability to add new rows. What should I do so that I can add new records to the list displayed? Access didn't have a problem with it. I have tried changing it to a view with TOP 100 PERCENT option and that did not work either. This is the only column in this table. It is unique and is the primary key for this table.

Please help me in finding a resolution for this issue.

Regards,
Helios

Speed and file size issues are driving me to consider a server (MYSQL or SQL Server) back -end. I love Access of course, and want to keep the front end work anyway.
Can I be lazy and just link to my tables in the back ? Or must I dispense with bound forms and work at coding my data entry forms ?
I understand (tell me if I'm wrong ) that the speed issue with Access on a LAN is based on the entire MDB (it's already split) holding the data being brought over the network to the client computer. Will this happen anyway if the forms stay bound to a server backend ?

Hi All,

I just moved my tables to an SQL server, and linked my tables to my Access 2010 front end. In this db, I have a table for error logs, and used code I found on Allen Browne's site to log errors. It worked fine when the tables were in Access, but I get an error now that the tables are in SQL.

I fixed the original error, shown in Green below. but now I get the following error:

Quote: Error 3001 Invalid Argument This is my first go with an SQL backend, so I have absolutely no idea what I am doing, so I certainly don't know what I am doing wrong. Googling the error seems to come up with a ton of explanations that I either don't understand or that I don't think apply to my scenario.


	Code:
	Function ErrorLog(ByVal ErrorNum As Long, ByVal ErrorDes As String, _
                  strCallingProc As String, ModName As String, Optional bShowUser As Boolean = False) _
                  As Boolean
'--------------------------------------------
' Procedure : ErrorLog
' Author    : Allen Browne, allen@allenbrowne.com
' Date      : 8/3/2010
' Purpose   : Generic error handler.
' Arguments : ErrorNum - value of Err.Number
' ErrorDes  : value of Err.Description
' strCallingProc: name of sub|function that
'                 generated the error.
' bShowUser : optional boolean: If False,
'             suppresses display.
'
'Logs errors to table "tblErrorLogID".
'--------------------------------------------
    On Error GoTo Err_ErrorLog
    ' Purpose: Generic error handler.
    ' Logs errors to table "tblDatabaseErrorLog".
    ' Arguments: ErrorNum - value of Err.Number
    ' ErrorDes - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne, allen@allenbrowne.com
    Dim strMsg As String      ' String for display in MsgBox
    Dim rst As DAO.Recordset  ' The tblErrorLogID table
    Select Case ErrorNum
            'Case 0
            'Debug.Print strCallingProc & " called error 0."
            'Case 2501            ' Cancelled
            'Do nothing.
        Case 3314, 2101, 2115    ' Can't save.
            bShowUser = True
            If bShowUser Then
                strMsg = "Record cannot be saved at this time." & vbCrLf & _
                         "Complete the entry, or press  to undo."
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
        Case Else
            If bShowUser Then
                strMsg = "Error " & ErrorNum & ": " & ErrorDes
                MsgBox strMsg, vbExclamation, strCallingProc
            End If
'Here is where I added the change
            Set rst = CurrentDb.OpenRecordset("tblErrorLogID", , dbAppendOnly, dbSeeChanges)
            rst.AddNew
            rst![ErrorNumber] = ErrorNum
            rst![ErrorDescription] = ErrorDes
            rst![ErrorDateTime] = Now()
            rst![ErrorProcedure] = strCallingProc
            rst![ShowUser] = bShowUser
            rst![ModuleName] = ModName
            rst.Update
            rst.Close
            ErrorLog = True
    End Select
 
Exit_ErrorLog:
    Set rst = Nothing
    Exit Function
Err_ErrorLog:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
             "Please write down the following details:" & vbCrLf & vbCrLf & _
             "Calling Proc: " & strCallingProc & vbCrLf & _
             "Error Number " & ErrorNum & vbCrLf & ErrorDes & vbCrLf & vbCrLf & _
             "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "ErrorLog()"
    Resume Exit_ErrorLog
 
End Function



Hi,

I have an Access front end linking to a SQL Server backend via an ADP file.

When I edit a record, I am having trouble refreshing the listbox that displays that same record. I am trying to use the .Requery method (see code below)

Can anyone suggest a better way?

Thanks

Mark


Private Sub cmdEditProg1_Click()

'Edit the Strategic Programme Record

strQuery = "SELECT * FROM Tbl_StrategicProgrammes" & _
" Where ProgNo = " & TxtProgNo

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strQuery, cn, adOpenDynamic, adLockOptimistic

If Not rs.EOF Then

rs.Fields("ProgName").Value = txtProgName
rs.Update
MsgBox "The Strategic Programme has been modified", vbInformation, "PMO"

End If

rs.Close
Set rs = Nothing
Set cn = Nothing

LstProg.Requery

End Sub

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

G’day all.

From the point of an absolute novice in this area I seek information.

Overall requirement is security when a user tries to log in to an Access 2003 front end which is linked to a SQL Server backend. The SQL server version is unknown but will probably be the second latest, whatever that happens to be.

The ultimate goal is to put the primary responsibility of security on to SQL server to prevent a user from opening any Access front end and reading data. I specifically do not want to store either the user name or their password in the Access front end.

Once SQL server allows entry I will probably write my own internal security for the current Access application, who goes where who sees what…that sort of thing.

I will not be able to test any of this for maybe one or two weeks, we have to setup a hardware/software system.

Security is the name of the game. Suggestions, opinions, links and code are welcome.

Thank you and regards,
Chris.


Not finding an answer? Try a Google search.