Link SQL Server to MS Access frontend

Hi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
PrathmeshHi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
Prathmesh

Most likely the issue is that there is no primary key that Access can identify. By default, access will open an ODBC record set in update mode (you can add, delete, or modify the records that you see). But if access can not identify a primary key (or if none exists), then access will open the recordset in read only mode.

As a side note, sometimes even if you have a primary key, Access will "guess" the wrong primary key. In that case all the records will appear as "#DELETED" in the recordset view.

You may want to consider an Access Data Project (ADP) which uses ADO (not ODBC) to connect to a SQL back end. Access is a wonderful tool and a fine way to generate reports, but it does have some limitations.

Regards,

hmscott|||Just a couple of things you might want to look at:

1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?

In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.

I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.

I hope any of this helps.|||Hi hmscott and pkstormy,
hmscott you were spot on. The lookup table did not have any primary key. Yesterday late night i was trying to get this running and by chance hit upon this solution and in the morning saw your reply. Tks for the "deleted" reference, it is a good thing to keep in mind.

pkstormy, tks for all the ideas. I will record them and use them for future troubleshooting. As for the database, tks a lot for that too. I will definately go through it and try to incorporate those suggestions.

Initially the frontend-backend was Access but the functionality in my database outgrew Access so we decided to move to SQL Server. The emphasis now is move all the 4-5 systems that we have in the organization to SQL Server. Once that is done, then upgrading the frontend, based on all the suggestions, is on the agenda. Thanks for the help guys. Much appreciated.

Cheers.
Prathmesh

Link from SQL server to oracle

Hi
What is the best and most robust/recommended way to link SQL server 2000
to oracle 9i DB?
We are planning to send daily load across the link and do not want it
to fail.
Tks
Mangesh
I used linked servers for that. Worked very well.
HTH, Jens Suessmeyer.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2C3C1647-C94B-4C4A-BC85-389058124638@.microsoft.com...
> Hi
> What is the best and most robust/recommended way to link SQL server 2000
> to oracle 9i DB?
> We are planning to send daily load across the link and do not want it
> to fail.
> Tks
> Mangesh

Link from SQL server to oracle

Hi
What is the best and most robust/recommended way to link SQL server 2000
to oracle 9i DB?
We are planning to send daily load across the link and do not want it
to fail.
Tks
MangeshI used linked servers for that. Worked very well.
HTH, Jens Suessmeyer.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2C3C1647-C94B-4C4A-BC85-389058124638@.microsoft.com...
> Hi
> What is the best and most robust/recommended way to link SQL server 2000
> to oracle 9i DB?
> We are planning to send daily load across the link and do not want it
> to fail.
> Tks
> Mangesh

Link from SQL server to oracle

Hi
What is the best and most robust/recommended way to link SQL server 2000
to oracle 9i DB?
We are planning to send daily load across the link and do not want it
to fail.
Tks
MangeshI used linked servers for that. Worked very well.
HTH, Jens Suessmeyer.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2C3C1647-C94B-4C4A-BC85-389058124638@.microsoft.com...
> Hi
> What is the best and most robust/recommended way to link SQL server 2000
> to oracle 9i DB?
> We are planning to send daily load across the link and do not want it
> to fail.
> Tks
> Mangesh

line to extend to edge of page when using a matrix

I have a few lines in my report page headers and

page footers. On the report I have a matrix, which extends the report horizontally across

two pages or more. How can I get the lines to extend to the edge of the

report?

i'm still having trouble with this, does anyone have any ideas?

line to extend to edge of page when using a matrix

I have a few lines in my report page headers and

page footers. On the report I have a matrix, which extends the report horizontally across

two pages or more. How can I get the lines to extend to the edge of the

report?

i'm still having trouble with this, does anyone have any ideas?

Line across Table

How do I get a line to go across the detail in a table?
If I set the underline for each cell, I get the value in each cell
underlined, I don't want that. I want an underline to go across the detail
section of my table. So, the user can see a line between each row in the
detail.Mike, you need to set the border style for the detail row cells. Select the
cells then set the bottom to solid.
=-Chris
"Mike L" <MikeL@.discussions.microsoft.com> wrote in message
news:E745059D-A68A-4BCD-B1C1-689956BA1351@.microsoft.com...
> How do I get a line to go across the detail in a table?
> If I set the underline for each cell, I get the value in each cell
> underlined, I don't want that. I want an underline to go across the
> detail
> section of my table. So, the user can see a line between each row in the
> detail.

Line 1: Incorrect syntax near

Hello All,

Me saying " has any body come across such error would be
underestimating".

Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."

Explaining you the scene is the following Stored Proc.

This stored proc is execute from a VB code in the .net application as
like: -
{Try
Connection.Init_Variables()
cn.ConnectionString = Connection.gstrConnection
ResDb.ConnectionString = Connection.gresConnection
cn.Open()
With sqlCmd
..Connection = cn
..CommandText = "DSP_Get_Required"
.CommandType = CommandType.StoredProcedure

..Parameters.Add("@.ActionId", SqlDbType.Int, 9).Value = ActionID
..Parameters("@.ActionId").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.PersonID", SqlDbType.Int, 9).Value = PersonID
..Parameters("@.PersonID").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.ReturnMessage", SqlDbType.VarChar, 1000).Value =
ReturnMessage.ToString
..Parameters("@.ReturnMessage").Direction =
ParameterDirection.InputOutput

..Parameters.Add("@.Exists", SqlDbType.Bit, 1).Value = Exists
..Parameters("@.Exists").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.Days", SqlDbType.Int, 9).Value = 0
..Parameters("@.Days").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.StartDate", SqlDbType.DateTime).Value = Now()
..Parameters("@.StartDate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.EndDate", SqlDbType.DateTime).Value = Now()
..Parameters("@.EndDate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.OutCome", SqlDbType.VarChar, 20).Value = "Stop"
..Parameters("@.OutCome").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.Evaluate", SqlDbType.Int, 9).Value = 0
..Parameters("@.Evaluate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.DbName", SqlDbType.VarChar, 100).Value =
ResDb.Database.ToString
..Parameters("@.DbName").Direction = ParameterDirection.InputOutput

..ExecuteReader(CommandBehavior.Default)
}

On Execution I get the subjected Error "Line 1: Incorrect syntax near
'Actions'."
Any Ideas from your all experience to get away from this error will be
helpful. Look forward to read somebody soon.

Stored Proc:-
{SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,
N'ISPROCEDURE') = 1)
DROP PROCEDURE dbo.DSP_Get_Required_ActionS
GO

CREATE PROCEDURE DSP_Get_Required_ActionS
@.ActionID INT OUTPUT,
@.PersonID INT OUTPUT,
@.ReturnMessage Varchar(1000) OUTPUT,
@.Exists BIT OUTPUT,
@.Days INT OUTPUT,
@.StartDate DATETIME OUTPUT,
@.EndDate DATETIME OUTPUT,
@.OutCome VARCHAR(20) OUTPUT,
@.Evaluate INT OUTPUT,
@.DbName VARCHAR(100) OUTPUT
AS

SET NOCOUNT ON

--DECLARE @.PopulateSQL AS NVarchar(4000)

DECLARE @.Rule_ID AS NUMERIC(9)
DECLARE @.Curr_ActionSubType AS VARCHAR(20)
DECLARE @.Eval_SubType AS VARCHAR(20)
-- DECLARE @.OutCome AS VARCHAR(20)
-- DECLARE @.Evaluate AS INT
-- DECLARE @.Days AS INT
DECLARE @.Message AS VARCHAR(1000)
DECLARE @.Mandatory AS BIT

-- This is the variable used to interpret the Precedant subtype
DECLARE @.Prec_Subtype AS VARCHAR(20)

-- DECLARE @.Exists AS BIT --this is supposed to be the deceision maker
variable to be used within the precedant check.

DECLARE @.Precedant_SubTypes_Cnt AS INT--This is the variable used to
recordcount the Precedant Subtypes to be checked
DECLARE @.Counter AS INT -- Counter used to loop through the Table of
precedant Subtypes.
DECLARE @.ROWCOUNT AS INT -- Temporary ROWCOUNT

SET @.Counter = 1

--Process to retrive @.Curr_ActionSubType Variable
CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))
EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @.DbName
+'.resadm.action WHERE '+@.DbName+'.resadm.action.Actionid = '+
@.ActionID+' AND ' +@.DbName+'.resadm.action.status =''A''')
SET @.Curr_ActionSubType = (Select ActionSubType from
#Curr_ActionSubType)
DROP TABLE #Curr_ActionSubType

--Process to retrive @.StartDate Variable
CREATE TABLE #StartDate(StartDate DATETIME)
EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,
'+@.DbName+'.Resadm.Action.DateofAction + '' ''+
'+@.DbName+'.Resadm.Action.TimeOfAction)
FROM '+@.DbName+'.resadm.action
WHERE '+@.DbName+'.resadm.action.Actionid = '+ @.ActionID +' AND
'+@.DbName+'.resadm.action.status =''A'''
)
SET @.StartDate = (Select StartDate from #StartDate)
DROP TABLE #StartDate

SET @.Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)
SET @.Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE
Curr_Subtype = @.Curr_ActionSubType and Status <>0 GROUP BY
Evaluate_Subtype)
SET @.OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY OutCome)
SET @.Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Evaluate)
SET @.Days = (SELECT Days FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Days)
SET @.Message = (SELECT Message FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Message)
SET @.Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE
Curr_Subtype = @.Curr_ActionSubType and Status <>0 GROUP BY
Optional_Mandatory_Precedant)

-- create the temporary table for the Subtypes to be evaluated
CREATE TABLE #Preceding_SubTypes_Details
(SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,
RULE_ID NUMERIC(9),
SubType VARCHAR(20),
)

-- insert the current subtype that needs to be evaluated.
INSERT INTO #Preceding_SubTypes_Details
SELECT Rule_ID, Prec_Subtype
FROM Rules_Details
WHERE Rule_ID = @.Rule_ID

-- create the History table for Reference
--sk/*Modified to accomodatethe need ot dynamic database name to
retrive from the different Resman databases
CREATE TABLE #dsHistory ( ActionID INT,
PersonID INT,
ActionTypeID VARCHAR(1),
DateofAction DATETIME,
Status VARCHAR(1),
Subtype VARCHAR(6),
ActionTypeName VARCHAR(30),
ActionSubtypeID VARCHAR(6),
EffectCandidateCurrentState VARCHAR(10),
TaxCode VARCHAR(6)
)
EXEC ('INSERT INTO #dsHistory SELECT
'+@.DbName+'.Resadm.Action.ActionID, '
+ @.DbName+'.Resadm.Action.PersonID,
'+@.DbName+'.Resadm.Action.ActionTypeID, '
+ 'CONVERT(DATETIME, '+@.DbName+'.Resadm.Action.DateofAction + '' ''+
'+@.DbName+'.Resadm.Action.TimeOfAction)DateofActio n, '
+ @.DbName+'.Resadm.Action.Status, '+@.DbName+'.Resadm.Action.Subtype, '
+ @.DbName+'.ResAdm.Action_Types.ActionTypeName,
'+@.DbName+'.Resadm.Action_subtypes.ActionSubtypeID , '
+ @.DbName+'.Resadm.Action_subtypes.EffectCandidateCu rrentState,
'+@.DbName+'.Resadm.Person.TaxCode '
+ ' FROM '+@.DbName+'.Resadm.Action '
+ ' INNER JOIN '+@.DbName+'.ResAdm.Action_Types WITH(NOLOCK) '
+ ' ON '+@.DbName+'.ResAdm.Action_Types.ActionTypeID =
'+@.DbName+'.Resadm.Action.ActionTypeID '
+ ' INNER JOIN '+@.DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '
+ ' ON '+@.DbName+'.Resadm.Action.subtype =
'+@.DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '
+ ' INNER JOIN '+@.DbName+'.Resadm.Person WITH(NOLOCK) '
+ ' ON '+@.DbName+'.Resadm.Person.PersonID =
'+@.DbName+'.Resadm.Action.PersonID '
+ ' WHERE '+@.DbName+'.Resadm.Action.actionID <>
CONVERT(VARCHAR,'+@.ActionID+')'
+ ' AND '+@.DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@.PersonID+')'
+ ' AND '+@.DbName+'.Resadm.Action.Status =''A'' '
+ 'AND (CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.Dateof Action +''
''+ '+@.DbName+'.Resadm.Action.TimeOfAction) > '
+ ' ISNULL(( SELECT
MAX(CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.Dat eofAction + '' ''+ '
+ @.DbName+'.Resadm.Action.TimeOfAction)) '
+ ' FROM '+@.DbName+'.Resadm.Action '
+ ' WHERE ('+@.DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@.PersonID+')) AND '
+ ' ('+@.DbName+'.Resadm.Action.Subtype =''ZERO'') '
+ ' AND ('+@.DbName+'.Resadm.Action.Status=''A'')),0)) '
+ ' ORDER BY CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.DateofA ction +
'' ''+ '+@.DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')

--sk*/
SET @.EndDate = (SELECT ISNULL((SELECT DateOfAction
FROM #dsHistory
WHERE SubType = @.Eval_SubType), getdate()))

-- set the rowcount to retrieve the number of check to be carried out

SET @.Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM
#Preceding_SubTypes_Details)

WHILE @.Counter <= @.Precedant_SubTypes_Cnt
BEGIN
SET @.Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details
WHERE SubTypes_LIST_ID = @.Counter)

SET @.ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =
@.Prec_Subtype)

IF @.ROWCOUNT > 0
BEGIN
SET @.Exists = 1
END

IF @.ROWCOUNT = 0
BEGIN
IF @.Mandatory = 1
BEGIN
SET @.Message = @.Message + ' ' + @.Prec_Subtype + ' Is Missing; '
SET @.Counter = @.Precedant_SubTypes_Cnt
SET @.Exists = 0
END
ELSE IF @.Mandatory = 0
BEGIN
SET @.Message = @.Message + ' ' + @.Prec_Subtype + ' Is Missing; '
SET @.Exists = @.Exists
END
END

SET @.Counter = @.Counter+1
END

IF @.Exists = 0
BEGIN
EXEC(
' UPDATE '+@.DbName+'.Resadm.Action '
+ ' SET '+@.DbName+'.Resadm.Action.Status = ''I'' '
+ ' WHERE '+@.DbName+'.Resadm.Action.ActionID = '+@.ActionID+' SET
@.ReturnMessage = '+@.Message
)
END
ELSE
IF @.Exists = 1
BEGIN
SET @.ReturnMessage = @.Message
END

IF @.Rule_ID = Null
BEGIN
SET @.ReturnMessage = 'Validation Rule Not Present'
END
-- Select 'Exist value : ', @.Exists, 'Return message is : ',
@.ReturnMessage
DROP TABLE #Preceding_SubTypes_Details
DROP TABLE #dshistory

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO}Sandesh (sandesh27uk@.gmail.com) writes:
> Me saying " has any body come across such error would be
> underestimating".
> Well I am getting a very peculiar and unique error "Line 1: Incorrect
> syntax near 'Actions'."

You will have to excuse me, but the error looks very standard and
commonplace to me.

This is what you get when you entagle yourself into a web of dynamic
SQL. Look at http://www.sommarskog.se/dynamic_sql.html#Dyn_DB for
suggestions of alternative strategies.

My bets goes on the UPDATE statement at the end. Overall, including
the database name in the column qualifications is overkill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

LIMITs on MIRROR with SQL Server SE

Hi folks. I'm trying to clear up some licensing confusion I"ve come across. First, if I use MIRROR on SS SE I am limited to a single REDO thread and Sycn mode. I see that on the Web. Will the singel REDO thread hurt the ability for the Secondary to keep up? Any one done any tests?

My confusion comes from a document that came across my desk a long time ago that showed that if you use MIRROR on SQL Server SE you are limited to 4 processors for the entire cluster. So if you wanted a balanced failover you would have to have two 2-ways in essence. Is this true? Is it not longer true? I can't seem to find any mention of this....did someone give me bad advice? Help! Thanks.

Licensing will not hurt any performance, only the number of transactions will have the recovery of REDO and UNDO process during the recovery.

|||In standard edition you wont get parallel redo and database snapshots features in the case of database mirroring.