Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Determine Last Access Of A Database Or Table

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)?
I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table.
I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience.
Any info will help greatly!


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.

I need to determine the following about the current authenticated Windows domainuser who is trying to access a SQL Server via a trusted connection.

1 Has the current user been granted login access to the trusted SQL Server?

2 Has the current user been granted access to a specific database?

3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?


View Replies !   View Related
Query To Determine Public Access
Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks

View Replies !   View Related
A Query To Determine A Role Or Id's Access To An Object... Do-able?
Hey there.
I must write a stored procedure to update a table and want to make sure the ID used in both Acceptance and Production will have the necessary access.  The ID will inherit it's access to the resource though a ROLE.
Wondering if there are queries out there that can traverse for example, the roles/id's that have access to it... Thx!

View Replies !   View Related
How To Determine Last LSN For Database

What is the most reliable way to determine the last LSN of a database? I've looked in sys.database_files to no avail. I've also looked in msdb.dbo.backupset which is accurate but only based on backups already performed not the current state of the database.


View Replies !   View Related
Determine If Autoincrement Is Set In A Table, Form
I want to access sqlserver table properties from .How do i know that a table defined in sqlserver has autoincrement field or not.Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.
I am not being able identify this property from codes.Please help.thank you

View Replies !   View Related
Determine Table Load Order
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies?



View Replies !   View Related
Query To Determine Table Size
Hi all,
I am looking for a query to determine the exact size of a large table.


View Replies !   View Related
Determine Current Table Of Trigger
Good Morning

Is There anyway to determine the table you are referencing in a trigger's inserted / deleted tables?

i.e would this be possible
print @Table_Name + " was Updated"

Where @Table_Name is the table on which the trigger is created

(bad example I know but you get the idea)

Any input will be appreciated.

View Replies !   View Related
How To: Determine If A Table Exists Using SQL Or TSQL.
I need a SQL or TSQL command(not a stored procedure) that will determine if a table exists (TBL_PARAMETERS).The command needs to return a 1 if the table exists or0 if it dose not exist.


View Replies !   View Related
How To Determine Length Of Table Row In Sql 2005
How, other than by doing the addition on my own, canI detemrine the length of a row in a table?



View Replies !   View Related
Access A Table From A Different Database
Hi all,
I'm writing a procedure that require to access a table from a different database but from the same server. Can the procedure able to read this table. If so how should my procedure use that table?
Thank you for all your help.

View Replies !   View Related
How To Access Table In Different Database?
I have 2 databases in SQL Server named Database1 and Database2. I have logged in Database1. I would like to see the records of the Table which is present in Database2. Could anyone please tell me How to access the tables in Database2 from Database1?

Sathish kumar D

Sathish kumar D

View Replies !   View Related
How To Access Table From Different Database
i am using stored procedure which put data in another table fron different database.

so i am using servername.databasename.dbo.tablename.

if I am change the servername then i have to edit the Stored procedure.
Is there any way to access server that if server name is chane then also i dont have to change stored procedure.

Like in c we use #define where we change value of variable that is reflected in whole program.

View Replies !   View Related
Need To Determine If Records Are Already In Database
Problem: I can add 4 items to a SQL 7.0 database but I need to redirect the user if all 4 items exist in the same Record elsewhere in the Database.

Clearly I need some kind of statement like "if category1 = var1 AND category2 = var 2 AND..." but don't know where to put it.


strSQL = "SELECT * FROM tblUserQuarter"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn,adOpenStatic , adLockOptimistic

'Insert choices from report.asp into tblUserQuarter
objRS("cboFY") = Request.Form("cboFY")
objRS("Quarter") = Request.Form("Quarter")
objRS("ID") = Request.Form("ID")
objRS("Report") = Request.Form("Report")

Set objRS = Nothing
set objConn = Nothing

View Replies !   View Related
Determine Date Database Was Last Used ?
Hi There

I am trying to establish the date a database was last used.

Atfirst i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.

I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database,i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.

And i need to use tsql, checking audit logs etc is not viable, can anyone help ?


View Replies !   View Related
How Can I Determine The Size Of A Database?
Simple question...

When I use the full version of SQL Management Studio, I can run a report to view the size. How can I do this with SQL Mgmnt Express?

View Replies !   View Related
How To Determine Table And Column Names In A Trigger?
Hi all,

I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.

I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.

Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?


View Replies !   View Related
Transferring ACCESS DataBase(ABC.mdb) To SQL Table Using C#
I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming)
i need to use this query on access database

which will when run create 4 fileds




And sorce database

I m very new in this stuff. so please please any one have any sample code for this then send me !
please i realy need it
My email id is
Thanks lot!!!!

View Replies !   View Related
Last Access Time For A Database/Table

Hi there is there any way in tsql that I can tell when a database/table was last accessed? We have a lot of *** on our servers that need cleaning up.

If it is not possible could I put a trace on the servers that will tell me if any access has occured and run this for a couple of weeks for instance to see if any access has occured?



View Replies !   View Related
Need Query To Determine If Database Empty
I would appreciate a query to determine if a database is empty?

Empty meaning that no user tables have any data in them. I expect the system tables in the database
to have data in them.

An alternative idea, but not exactly the same thing, would be the ability to determine it the database
is in its pristine state (creation state, but not yet user modified).

Thanks in advance for your advice/help!!

Gary A.

View Replies !   View Related
Determine When A New Record Is Added To A Database
I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?


View Replies !   View Related
How To Determine Sp Caller Current Database?
When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

View Replies !   View Related
SP To Determine Layout, Population And Sample Data In Table(s)
Hi! I am new to SQL Server... looking for some veteran assistance.

"Data Integrity Report"

I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).

The cursor/report needs to have the following columns:

Ordinal_Position (I.E. Column number)
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)


Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):

Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345

Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).

Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.

Sorry for the long post, and thanks in advance!

View Replies !   View Related
How To Access The Info Of A Table Created In Other Database
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.I tried the above query with changing the table_name to 'db2..tab_db2'but went invain.sysobjects also dint work..Any help on this will be appreciated..regards,Sathya V

View Replies !   View Related
How Do I Determine The Backup Date Of A Restored Database..?
Hi All,I have to a few backups restored from a set of backup files. Thebackup files have been removed from the drive because of some storageconstrains...For some data comparison reasons, I need to figure out the actual dateof these backup files that is used to restore these databases. Isthere any system table I can query to figure the actual backup timesof these databases...Any help will be much appreciated.. Thanks in advance.- Aravin

View Replies !   View Related
Cannot Determine How To Set Owner To Install Database Design
My compatibility settings are fine. Anyone know how to to this?

View Replies !   View Related
Stored Procedure Can't Access Table On External Database

I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.

My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').

I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.

Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.

Example of stored procedure on external database:

/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/
@Username Varchar(20),
@Password Varchar(20)
Select User_ID From UserList
Where User_Username = @Username

Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!

Any help would be greatly appreciated as I am completely stuck.


View Replies !   View Related
How To Import Access Table Into SQL Express 2005 Database?
Can anyone help me import a ms access (97) table into a sql 2005 express database? The sql database already contains other tables but I would like to add some of my older access tables to this database without having to re-enter all the data. Please help! Thanks!

View Replies !   View Related
Denormalized Access Table To Normalized Database TableS

I am pretty new to SSIS, so please excuse me if this is a trivial question.

I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.

I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?

I was thinking I need to loop through eachDISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.

The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, appreciated!!



View Replies !   View Related
Connecting To SSCE3.5 Database Using Access Link Table?

I'm a long-time Access developer who is looking to migrate his approach to SQL Server CE 3.5 instead of having all these MDBs. One thing that I do like is using Access to surf through databases; it's a lightweight way to see the data without having to fire up the overhead of Visual Studio etc. Plus there's the QBE grid for easy query design for those of us whose tool-less SQL coding is a bit rusty...

Anyway, I was wondering whether it was possible to "attach" to a SSCE database so you can see it in Access. I've tried but haven't found a way of doing it. Any thoughts? Or is this an intentional lack of capability so we have to use a more robust tool such as Management Studio or Visual Studio?

Thanks in advance!

View Replies !   View Related
Can Never Access Database Table From Code After Copying To Another SQL Server...
I am not sure I understand the problem I am causing, but I am a beginner!

Here's the situation: I have a table located on MS SQL server database number 1. Said table, which we'll call WIDGET_PRICES, is accessed regularly by my existing source code and has no problems.

At some point, I decide to move operations to MS SQL database number 2 and do a very simple database copy of WIDGET_PRICES from database 1 to database 2 using the Microsoft SQL Server Management Studio.

The end result, inevitably, is that my source code can no longer access the very same table as it is located on the new database server. The code hasn't changed, it's still trying to access WIDGET_PRICES as always. And, from what I see on my screen through Management Studio, WIDGET_PRICES appears just fine.

An example error is the one I just got:

Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name 'YB_ITEMS'.

/yardbark/tampabay/header.asp, line 27

The only clue is that while my transferred tables often look named like "database1.WIDGET_PRICES on database 1, they wind up looking like database2.WIDGET_PRICES on dabase 2.

I include a little more detail and screenshots of the tables in questions at this web page.


View Replies !   View Related
Way To Determine Which Logins Have Write Permissions To Database On Sever
Hi there,

Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.

Any help is appreciated. Many thanks!

View Replies !   View Related
Waht Is Tempdb Database Used For? And How Can We Determine What Files Can Be Deleted From It?
Hi, all experts here,

Thank you very much for your kind attention.

Just found that my tempdb is always full whenever I run a query against a large database.Could please any experts here give me any advices on whatis tempdb database used for and how to determine what files can be deleted from it?

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

View Replies !   View Related
VB6 && VB2005 Runtime Error When Writting To Access Database Table
In both VB6 and VB2005 Both are loaded on same computer. When attempting to write to an access database table using ADO I get a runtime error: -2147217887(80040e21) "mutiple-step operation generates errors. Check each status value. " Reading the database is no problem. I only get the errors when writting to database. I have used the following code forever in VB6 and never got this error. Now that I have Visual Studio 2005 loaded on the same machine. I get this error. I get the exact same error when using this code in VB2005. I know VB6 but am just learning 2005. The following code is writting to a database table from various user imputed text and combo boxes. I used basicly the same code in VB2005 and get the same exception error. This code works on machines that do not have VB .net loaded. How do I fix this. My application is useless unless the user can make changes to their data.

Public Function ActionToDb()
Dim noPo As Integer
Dim SQL As String, rst As ADODB.Recordset, dataSourceName As String

dataSourceName = DBPATH & "LVBURV Database.mdb"
dbConn.ConnectionString = "DBQ=" & dataSourceName & ";Driver={Microsoft Access Driver (*.mdb)}"

On Error GoTo ErrorHandler

Set rst = New ADODB.Recordset
With rst
SQL = "Select [mLVbuNo], [mStratInit], [mMerticLv],[mSubMetLv],[mTaskAction],[mImpact]," & _
"[mStatus], [mOwner], [mTitle],[mOrigDate],[mCurrentDate],[mCopmpleteDt],[mComment],[mSource]," & _ "[mStatColor],[mHyperlink],[CompCheck] From [tblMasterActionItem]"
.CursorLocation = adUseClient
.Open SQL, dbConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
noPo = .RecordCount
'If .RecordCount > 0 Then
rst.Fields("mLVbuNo") = giRecNo
rst.Fields("mStratInit") = frmActionDe.cboStatInit.Text
rst.Fields("mMerticLv") = frmActionDe.cboMetricLever.Text
rst.Fields("mSubMetLv") = frmActionDe.cboSubMet.Text
rst.Fields("mTaskAction") = frmActionDe.txtTask.Text
rst.Fields("mImpact") = frmActionDe.txtImpact.Text
rst.Fields("mStatus") = frmActionDe.txtStatus.Text
rst.Fields("mOwner") = frmActionDe.cboOwn.Text
rst.Fields("mTitle") = gstOwnT
rst.Fields("mOrigDate") = frmActionDe.txtOrigDt.Text
rst.Fields("mCurrentDate") = frmActionDe.txtCurDt.Text
rst.Fields("mCopmpleteDt") = frmActionDe.txtCompDt.Text
rst.Fields("mComment") = frmActionDe.txtComments.Text
rst.Fields("mSource") = frmActionDe.cboSource.Text
rst.Fields("mStatColor") = frmActionDe.txtStatColor.Text
rst.Fields("mHyperlink") = frmActionDe.txtHyperlink.Text
If optCompCheck.Value = True Then
rst.Fields("CompCheck") = True
rst.Fields("CompCheck") = False
End If
'End If
End With

On Error Resume Next
Set rst = Nothing
Set dbConn = Nothing
Exit Sub
WriteToErrorLogEnd Err.Description & " in ActionToDb"

End Function

View Replies !   View Related
Query Report Server Database To Determine Permissions For Reports
Anyone know of a way to do this? I'm looking over the tables and it's not obvious to me how to list the AD groups I've authorized for reporting services. And then to list per group which reports they have access to.

View Replies !   View Related
Unexpected Error From External Database Driver (MS Access Linked Table)
OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)

User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:

"Unexpected Error from External Database Driver (22)."

A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).

Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.

Any help appreciated. I am not an Access guru.



View Replies !   View Related
How Can I Copy The Content Of MS Access Table In Database SQL Using Visual Studio Basic
Code Snippet

Hi there,

I'm struggeling for more than a week now with this problem, without a finding the solution.

I have two databases, MS Access and SQL Server 2005 Express Edition

Using a procedure in Visual Studio i would like tocopy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)

I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even anexeption has been raised), looking during debug, the DataSet seems filled though...

Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?

Note i woul have as afinal goalto get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).

the procedure goes like this;

' Create a connection to theMS AccessDatabase
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)

Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")

' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)

Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")

DaSQL.Update(DsSQL, "Tabella")

Note I tried also the following, withou a result;

DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")

Please is there someone who could respond !!???

View Replies !   View Related
Database Access Via COM Objects - V- Database Access Via Stored Procedures
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?

View Replies !   View Related
Determine Table Names And Column Names At Runtime?

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.


View Replies !   View Related
Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them
Access Connection

create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!

Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager thatI created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db

Any ideas please help

thanks in advance

View Replies !   View Related
Users' Access Linked Table From ACCESS To SQL 7.0 By ODBC
Hi Everyone,

I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.


View Replies !   View Related
Can I Access MS Access Table In A Select Query Of SQL Server
Is there a way tospecify a MS Access table (or query object) in the select query of SQL Server.


MSAccessTable (in file.mdb)




SQL query in SQL Server:

SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;


View Replies !   View Related
Cannot Get Access To My Access 2003 Database Tables
I developed a database with Access 2003 and everything was working good until my tech came in and reformated my hard drive and install a new Ghost image that met our company standards.

Now I cannot go in and make any changes to any of the tables, queries and forms. All of this started when a new Ghost image was installed on my pc.

The message I get when I try to open my database is "You do not have permission to run "tblSwitchboard." I get the same error message when I try to do anything at all on the database.

I am at a loss as to what to do. Please help.

View Replies !   View Related
Importing Access Table Into SQL Server 2005 Express Table And Adding One Field
Hi all,

Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference.However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.


View Replies !   View Related
Append Query From Access Table To Linked SQL Server Table Failing
Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?

View Replies !   View Related
ADOX To Create A DSNless Linked Table To A SQL Server Table In MS Access?
QUESTION: How do I use ADOX (VB) to create a DSNless linked table to a SQL Server Table in MS Access?

- Will need a code skeleton that satisfies all conditions above (Including the minimum table properties required).
- SQL Server Name: MySQLServer
- Database Name: MyTestDB
- Table Name: MyTestTable

View Replies !   View Related

Copyright 2005-08, All rights reserved