Access Form Locked after linked table from sql

Hi:
I am experencing very strange case.
I have two identical MS access application, only difference is one backend is MS database, another is MS Sql database.

When I run the application with the sql backend, one form just doesn't allow me to edit, even I can select value from a comboBox control.
(But the application with the MS database backend works fine).

I don't know what will casuse the problem, do I need to do something with the sql data table permmission or what?

Thank you very much.

Jt


Post your answer or comment

comments powered by Disqus
Hi,

I used ODBC to link the tables from SQL Server to MS Access.

When I link the table, it popup the message:

Select Unique Record Identifier

To ensure the data integrity and to update records, you must choose a field or fields that uniquely each record. Select up to ten fields.

It force me to set up at least 1 field for primary key in a table.

How can I skip it? I do NOT want to setup primary key of the fields in a table.

Please let me know, thanks.

I have a front end client that needs to be able to connect to both SQL Server and Access. I have no problems switching from Access to SQL Server but when I try and switch from SQL Server to Access every technique has failed.

Despite providing what I believe is the correct connection string (it's the one I use when initially linking the tables to an Access back end) when the TDF.Refreshlink code executes I'm prompted to provide the DSN source.

Here's my code:

For Each Tdf In Dbs.TableDefs
If Len(Tdf.Connect) > 0 Then

Tdf.Connect = "MS Access;" & "PWD=XXXXX" & ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
Tdf.RefreshLink

If Err 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next Tdf

Any and all advice will be greatly appreciated.

i have a pc which doesn't have sql server installed. is it possible to link tables from .mdf file sitting on the pc into access database?

I have made an ODBC link to a SQL database on our companies server so that I can interrorgate the table for information. This is working well and I can produce a form showing the data I want.

My question is if I delete any text from a field on the form it deletes the information on the server. Is there anyway of telling Access to read only and not write. Should I have imported the information rather than link it?

I'm nearly there just this final hurdle to overcome.

Graeteful for any help the experts can offer.

I am working on a project that uses a VB program and connects to a access database to store customer records ect.

Im adapting this to a website so customers can view there repair status. how ever in order to do this i have to link tables from the access database to the MySql databse required from the site.

When i make a new record in the program (inserted to access) it errors saying
"The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

I have followed the advise on the MySql website:
"After exporting your table to MySQL, use phpMyAdmin to make the field "auto-increment" - you do this using the "change" option on the page that shows the table's structure. You may have to make that field "unique" first. You then have to add a new field to the table. It doesn't matter where it goes (the last column is probably neatest) or what it's called, but it must be of type "timestamp". You can then make the link from Access to the table. When you add new rows to the table, leave the auto-increment field and timestamp field blank - MySQL fills them in for you."

Still no luck. Any ideas?

Cheers,
Justin

I created MS Access linked tables to sql server already. I got all tables I need into MS Access database from SQL Server. However, it is like a copy so all updates on SQL Server database tables will not update on MS Access database tables.

How do I make the automatic updates on MS Access database tables for all updates from SQL Server db tables?

Thanks very much.

Hi Guys,

i am having a right game trying to delete some records from a simple table on my SQL server.

Say for instance the table dbo_Resource contains these records, where tasknumber 4 has 3 persons against it e.g.


tasknumber |person
4 | paul
4 | andy
4 | steve

and tasknumber is the primary key (because Ms Access makes you set a primary key with a linked table from SQL server)

When i run the code below and combo0 = 4

' Delete selected record
SQ = "DELETE * FROM dbo_Resource WHERE tasknumber = " & Me.Combo0 & ";"

'Execute the SQL statement
DoCmd.RunSQL (SQ)


I get a run time error message consisting of:

3362 Single-row update/delete affected more than one row of an attached (linked) table. Unique index contains duplicate values.

Any ideas?

PS// if i use an internal table in access it works with no error message! but the table must go on the SQL server because multiple users will use the app.

Thanks for any advice.

Hi Guys, I need some help to understand this...

I have an application in Access 2000(front end) & I use linked tables from SQL 2000(Back end). I have a table test.. I'm tryin to update a bit field from 0 to 1 using an update query. It does not perform the update.

"Update tblTest set process = 1 where process = 0"
this is a query in access
can you please help me...what am i missing?

thanx
M

Hi!

I have upsized an access 2003 database to SQL. In this project I have a form with four subforms that displays some data.

In my original DB I could change data in the datasheets directly, but after the upsizing the datasheets is not editable. I cannot edit existing or insert a new row in the datasheet.

Is it possible to make some change so that the datasheet is opend for editing even if the underlying table is a linked table to SQL 2008?

I am using an Access 2010 database with linked tables from SQL Server 2008. I have a form that needs to get it's data from a table valued function in SQL. The function takes two integer parameters.

I was not able to convert the underlying SQL query to Access, and am therefore hoping to use a function in SQL to get the data I need.

The problem is I am not sure how to use the function as a recordsource for the form, as in (SELECT * FROM FnGetDecisionPoints(70,4))

How do I achieve this? Thanks for any help you can give me.

I have a couple of linked tables in my access database. These tables are linked to a SQL Server on the lan.

When I click on the table 1, I can see all data in 88 rows.

I have a 2nd table with 4000 rows. When I click on this table, I am seeing
the first 88 rows correctly, but the next 3922 rows are showing identicle
data? (Which ins't true) When I do a select on this table from SQL Server Management Studio, I can see all rows are NOT the same.

Any clues on this issue?

I am on Windows 7 and Office 2010 Professional. (SQL Server 2008)

I also have an XP running Office 2003. Same MDB file with linked tables, but
the issue DOES NOT exist on this. Maybe ODBC issue? Maybe Windows 7, or maybe Access 2010 issue.



Thanks,

Should this package be installed on my Windows 7 development workstation AND on the distribution Windows Server? Has anyone tried this?

The concern is that the Access 2007 Front-End linked tables using ODBC or DNS-Less connection on the Windows 7 Development Workstation will work the same when the Front-End is moved over to the distribution Windows Server.

Access 2010 with a Front-End connected to a Back-end via linked tables.
Used the Microsoft SQL Server Migration Assistant to migrate the Access 2010 Back-End to SQL Server 2008 R2.

Distribution of the Front-End uses Citrix. This means that Access 2010 front-end will be installed on a Windows Server to run. Access 2010 is not installed on each of the client's workstations.

Microsoft® SQL Server® 2008 R2 Feature Pack
http://www.microsoft.com/download/en....aspx?id=16978
Contains: Microsoft® SQL Server® 2008 R2 Native Client

Hi all,

I have faced with one problem that is how to export Tables from SQL server to MS-Access.

could anyone plz help me

Thanks,
Bhaskar.

In Access 2003, I could easily make linked tables (from SQL Server) read-only, as follows:


	Code:
	    Set con = db.Containers("Tables")
    Do Until rs.EOF
            Set doc = con.Documents(rs!Local_Table_Name)
            For Each grp In wrkDefault.Groups
                doc.UserName = grp.Name
                doc.Permissions = dbSecRetrieveData
 
                For Each use In grp.Users
                    doc.UserName = use.Name
                    doc.Permissions = dbSecRetrieveData
                Next
            Next
        rs.MoveNext
    Loop

Is there a way to get this same result from Access 2007?

Thanks!

Guys, need help for Multiple value in form. i have database with link backend table in the SQL server 2005. i would like to add as multiple value like in the Access table. but i cant do it when in link table in SQL 2005. thanks to help me... i am so appreciated.

I have a SQL 2005 table that is linked to an Access 2007 table. the SQL table has 179 records, but the Access table only has 49.

Why would Access limit the fields it pulls across in a linked table?

Any help or advice would be appreciated!

Hello,

Using linked tables from SQL Server in a form. Can't use trusted connection and don't want to save password in plain text. Is there still a way to get rid of the login popup?
Thanks in advance for any help!

Geremore

(Access 97) Access help says you can link tables from a password protected database, but that you have "provide" the password and that it is then stored with the connnection information for that link. Using the linked table manager, I am not prompted for the password, and am unable to establish the link, receiving instead an "invalid password" error; if I first make the link then establish the password, I get the same error as soon as I try to access a linked table. Any suggestions are appreciated.

Hi,

I want to know how to build a linked table to SQL Server view with DSN - less connection.I can not do this from menus and I hope I can do this with VBA code.

Thanks

Mark

hi,

i'm using access 2000 to create an application. i need to get the information of Staff, but the Staff informationis in Lotus Notes 6.

my problem is how to link table from Lotus notes document?

an for your information exporting from Lotus Notes then access 2000 link table to that exported file is not an option.

anyone help?

regards,
askaccess

When I link tables from outlook file I see many missing columns like categories etc.. but if I export the same outlook file to an access DB then I see all the columns.. I need to link the access DB to the outlook file without losing any columns.. I'm using access 2003. Please help..

How do I create copy of database then link all tables from another database using vba. Will need to look through all tables.

i am looking for code that will create a copy of a database, then link tables from another database.
K

Hi guys,
Could anyone give me a code to create linked table to SQL server using ODBC Connection string?

Thanks.

We have a series of very large Access db's (using linked tables from SQL).
We need to be able to audit/track all changes made to forms/reports/VBA (and ideally queries) by our programmers.
Data auditing is fine - have programmed that into it.
But how on earth do you audit programmatic changes.
Any clues from anyone would be greatly appreciated.

Many thanks


Not finding an answer? Try a Google search.