I am linking to an SQL table through an access database.
I need to add records to the SQL table through the Acess .mdb but
the linked table will not let me.
How can I fix this?
Any help is appreciated.
Kelley
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies View Related
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies View RelatedHi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Thanks
We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new
server which is Windows 2000 based and the domain is different from
the SQL servers domain. We then installed Access 2000 on Windows 2000
to use with terminal server. But I noticed that there was a problem
with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but
I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem.
In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies View RelatedI am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.
When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.
This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.
I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.
I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.
Here is the exact scenario:
Problem #1:
A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made.
I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)
Problem #2:
A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.
SQL Server version is 2000, with SP3a applied.
I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI;
Recordset Cursor type: adOpenDynamic
Recordset LockType: adLockOptimistic
I open the recordset using:
rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Problem # 1
After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:
rst.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Problem # 2
I set the recordsource using: (in the form_open)
Me.RecordSource = "Select * from mytable where myfield = 'data'"
In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.
Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.
Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?
Thanks!
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
View 1 Replies View RelatedHi everyone,
Well, it's a bit difficult to explain this issue. I have written an e-commerce asp.net web site the web site will be hosted on a regular web host that i bought from a hosting company. and we have 2 servers in our company which one of them has internal ip address and the other one has fixed ip address. I have to install my sql 2000 db on the first server(the one with internal ip), I wanted to know that how can I link the the data base to the website via the second server(the one with fixed ip address). I was thinking about using link servers in sql server....
Could any one told me that if it is a good idea or not????
Hello Team,
Could you tell whate are best way or infor regarding accessing SQL Server 2000 and DB2-AS/400 database?.
Thanks,
Ravi
Hello folks,
To begin with, I aplogize for not posting a pure SQL Server question in this forum. I thought some of you might be able to help me out.
I have an Access application (both front and back ends built in Microsoft Access). I don't have the source code which has access forms and the VB code. All I have is an .mde (Access executable) and .mdb (Access database) that carries the actual data. All the forms in .mde are linked to data in this .mdb
We are planning to convert the whole thing into ASP & SQL Server, but before it gets ready, we want to change the back end to SQL Server leaving the front-end App in Access. My plan is to make all the tables in my MDB to linked tables that point to SQL Server. But since my .mde already has linked tables pointing to the access tables in MDB, it is not allowing me to have a linked table pointing to another linked table.
Can anyone let me know if there is any workaround to achieve this? Your help will be very much appreciated.
Thanks,
RV
hi,
somebody asked me this question but I haven't succeeded to give a good answer...
The question was...
Access 2000 is a DBMS and Sql server is an RDBMS,so what makes sql server a RDBMS in context of Codd's rule?What are the properties that Access doesn't have to be a RDBMS?
May be this is not so important issue,if anybody wants to answer plz help,gurus are requested not to waste their valuable time....If any of the gurus come I would be delighted....:D
Hi,
I am part of windows domain administrator group and I want to access SQL Server 2000 database running on Windows 2000 server, how can I do that ?. I need to reverse a transaction in SQL Server DB.
Thank you
I am trying to convert some MS Access queries to SQL Server 2000 and normally i can get past any problems but so far i haven't found a solution to this one:
Code Snippet
SELECT FIRST([Column1]), [Column2], FIRST([Column3]), FIRST[Column4]
FROM QryIMadeAlready
GROUP BY [Column2]
the results are not number values so i can not use max/min as far as i know. an ex: of the results would be.:
Code Snippet
First Qry Results (QryIMadeAlready)
C1Value1 ** C2Value1 ** C3Value1 ** C4Value1
C1Value1 ** C2Value2 ** C3Value2 ** C4Value2
C1Value1 ** C2Value2 ** C3Value3 ** C4Value3
C1Value1 ** C2Value3 ** C3Value4 ** C4Value4
The Select First Qry Results need to be:
C1Value1 ** C2Value1 ** C3Value1 ** C4Value1
C1Value1 ** C2Value2 ** C3Value2 ** C4Value2
C1Value1 ** C2Value3 ** C3Value4 ** C4Value4
How do i get this to work in SQL Server 2000? t-sql?
WoFe
Hello,
In Microsoft Access, there is a feature to create relationships between tables. When creating these relations, you have the option to:
1. Establish the relation type. For example, 1 to many (primary key to foreign key)
2. Enforce referential integrity
3. Allow cascading updates
4. Allow cascading deletions
5. View these relations in a nice diagram
Does SQL Server 2000 contain this functionality? If so, where is this found in the Enterprise Manager? If not, what is the alternative to this very useful feature found in Access?
Thanks You For Any Help!
I have to replicate(bidirectional) data from Access(.mdb) to SQL Server .What are the possible ways of doing it? Are there any known issues?
Thanks in advance.
Hi there everyone, I have written a database system which tracks the performance of working in a shipping company in access. Im now rewriting the system in sqlserver and the only real problem I have found so far is that its difficult to estimate what kind of a server *cpu* & *ram* would be appropraite. The system currently performs transaction on my desktop machine in a second a quickest and 2 at slowest.
There are going to be about 500 users in 3 time zones so there will only really be about 300 max hitting the system in an hour.
I was looking at a Dell Poweredge server with twin P3's and half a Gb or ram would this be a good place to start from?
Any Advice would be great!!!
Steve
I have a ms access database on say c:database.mdb
I have a sql server 2000 running too.
I want to use some command line tool , like isql or osql to convert this database into a sql sever database.
How can I achieve this ?
Thanks in advance.
Alok.
Hi,
I need to migrate my access database to SQL2000 Server. I have three tables in my access database. In each table have 3000-6000 records.
So how to put all these records in to sql2000 server tables.
The table structure in Access database and sql2000 database are same.
Here core work is just insert all these 3000 - 6000 records (rows) from Access database to sql2000 database
Thanks in Advance!!
With regards
Shaji
hello sir
sir i have sql server 2000.
i want to use it in other pc's thats remotly access.
pls tell me stepls for remotely access.
my pc name is -----TIPL
and server name is---- COMP3TIPL1
and i want to use it other 3 pc's
TIPL1,TIPL2,TIPL3
pls help me.
gayatri
I have an Access 97 database that I connect to a SQL Server 2000through ODBC. I have to manually set up the ODBC connection on eachuser's machine if I want them to be able to use the application though.Is there a way to store the connection string within the Access DB soI don't have to touch the ODBC settings on each user's machine? Or isthere a different solution that I'm missing? There are way too manyfor me tosetup. Thanks in advance.
View 1 Replies View RelatedIs it possible to connect to Sql Server 2005 using an Access 2000 adp? Ikeep trying, but it doesn't want to accept my id, even though it is right.
View 2 Replies View RelatedI have installed the SQL Server 2000 and its up running.
I can connect to the database direct as administrator, but my test application(asp.net 2.0) returns an exception.
SqlConnection sqlConn = new SqlConnection("data source=10.61.90.24;initial catalog=testdb;persist security info=False;user id=sa;pwd=data;Type System Version=SQL Server 2000;");
sqlConn.Open();
I receive this exception:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Hi
I would like to get some advise on the capacity of concurrent users on Access 2000. Here is my situation:
- I have a vb6 application that connects to one database(currently is a access 2000 mdb) via Adodc .
- There will be 6-8 copies of this application running on different PCs and that database is shared .
- All 6 PCs read/write to that database concurrently.
I wonder would Access 2000 be a reliable choice?
Thanks for your time
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
Subject problem has me quite vexed.
I am receiving the following error when attempting to access reporting services... to sum things up real nice and tidy-
I get three login prompts - then the access denied response. It is almost as if it is unable to authenticate the user... anyway... here's the actual error response, I'd really appreciate any input/insight/resolution.
Server Error in '/Reports' Application.
Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.]
System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) +2014163
System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption) +1817
System.IO.Directory.GetDirectories(String path, String searchPattern, SearchOption searchOption) +36
Microsoft.ReportingServices.Diagnostics.Localization.GetInstalledCultures() +112
Microsoft.ReportingServices.Diagnostics.Localization..cctor() +66
[TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.Diagnostics.Localization' threw an exception.]
Microsoft.ReportingServices.Diagnostics.Localization.SetCultureFromPriorityList(String[] localeList) +0
Microsoft.ReportingServices.UI.GlobalApp.Application_BeginRequest(Object sender, EventArgs e) +157
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?
View 1 Replies View RelatedHi
I have problem in configuring my web project on Window server 2000. I have created the web application using Visual Studio 2005 tools. I have configured Window server 2000 with the .Net Framework Version 2.0. I can access the page which doesn't have database access.
The connection string in the web config file is giving me the problem that connection string can't be found. Does any one have any idea?
My database connection string looks like this.
<connectionStrings>
<add name="etzAusBldConnectionString" connectionString="Data Source=CASCADE;Initial Catalog=etzAusBldBaseData1;User ID=sa1;Password=****"
providerName="System.Data.SqlClient" />
</connectionStrings>
Moe
Hi,
I have managed to transfer the SQL SERVER 2000 database tables to access database by using DTS. But how can I transfer stored procedures in SQL SERVER to Access 2000
Hello,
I am working on a couple fo the ASP.NET walkthoughs and I would like to practice with a Database that I created in Access. I realize that I could use the Access DB, but I would like to learn to work with a more industrial strength DB. Is there a method to export to MSSQL server 2000 from Access ?
Regards,
James
Venkatesh writes "In MS-Access table a coulmn is specified as Auto number property.
I want to migrate this table into SQL Server.
We can create a new column that has identity property in SQL Server 2000, which simulate autonumber property of MS-Access.
My access table contains 700 records and I need to set the column (ListID) as Auto number. i.e., I m going to modify this
coulmn with identity property.
But I cant set the identity property for the existing column.
Can u pls send me the sql query that modify the existing column(ListId) with Identity property.
Regards
Venkatesh"
How do I write this query that was written in access to sql?
SELECT Max(tri_InsrTran.claimnum) AS MaxOfclaimnum, tri_InsrTran.trannum
FROM tri_InsrTran
GROUP BY tri_InsrTran.trannum;