I have migrated a database from Access to SQL. Promlem is that I cannot edit the records in the now
linked tables. We are still using the Access front end. Why can't we edit the records?
Thanks in advance.
Hi. Silly question time please.
I'm a newbie when it comes to SQL7.
I'm trying to connect via Access 97 VBA to a SQL server table. I can't seem to get it working at a basic level.
Has anyone got the basic syntax or sample piece of VBA code to connect to a SQL server. I've been searching everywhere to no avail so far.
Dave.
If i create ODBC links for a SQL server db in Access and make the file read-only, i find that 2 people cannot open teh file simultaneously...
any ideas why and any suggestions how to over come thi problem...any solutions will be highly appreciated
regards,
resh
I have an Access 97 database that I would like to "mirror" in my SQL server 2000 database.
Basically I would like SQL server to keep a current copy of an Access 97 database table which exists on remote machine on our network. Any time there is a change made to the Access table, I would like the SQL server to be updated automatically.
Is this something that is possible to do with replication/synchronization, or do I have to push the data up with another app?
Thanks
The website i'm building is using an access97 database. Actually the database isn't very big but i hope it will become. So i would like to convert it to SQL 7.0 which seems to be more powerfull. Is there a software which can do it automatically? What are the modifications needed by ODBC ?
Thanks !
When I try to link tables from Access97 to SQL7, all the columns have #deleted# in them. Does any one know why this is?
Thanks
Phil
I am trying to link a table from a MS SQL database into access97 thru ODBC. When I do this, I get the message "Can't define field more than once." When I look at the table in SQL, there are no duplicate field names, however, when I bring the table in to do a Crystal Report, I see there are 2 fields that have duplicate field names 6 times. (Evidently Crystal doesn't care about this as Access does.) Any clues on what is happening?
View 2 Replies View RelatedI would like to create a MSAccess97 front end for some SQL7 data.
I created an odbc source system dsn for the server holding the SQL7 data.
When I try to add a table to my Access97 database by linking it to a SQL7 table, the only choices presented me are tables in the default database for SQL7 on my server. How do I link to tables in another database on that server? Thanks.
When Access date/time data is transformed to SQL7, it seems to work fine as long as the data contains date information. But, if the data is time only, there is the 2-day diff. coming out of Access.
For date/time data in Access, day 0 is 12/30/1899--for SQL7 it is 1/1/1900.
So, it appears that DTS first converts Access date/time data to a date formatted string and then sends to SQL. This will only be a problem for times....8:00 AM is stored to SQL as 8:00 AM on 12/30/1899, not 1/1/1900. If I try to calculate hours or minutes from midnight (date/time 0) I get a negative number.
Has anyone else encountered this? I guess my choice is to modify DTS to correct for the dates or to run an update once the data is moved.
I`m trying out SQLServer7, have installed it on my workstation, have been able to open access97 and link to sql7 tables I created. Want another user to use access97 to set up her own database and link to my sql7 tables. Do I need to install anything from the sql7 beta disk on her desktop? Right now she can`t link a sql7 table to her access97 db. Thanks.
View 1 Replies View RelatedWe have an Access97 database with 122 tables. I try to use import and export wizard to import the data from access into SQL 2005 database. I find an interesting problem. When select all tables, the wizard give following errors:
Pre-execute (Error)
Messages
Error 0xc0202009: {DBD1EAB5-7865-4B89-A7BB-DDC8507D8119}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - PlanClasses" (5206) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
But if I divide importing as 2 steps:
1. import all tables before PlanClasses, it works.
2. import all tables after PlanClasses including PlanClasses, it works.
If I just import PlanClasses itself, it works, but I can not import all tables at once. Interesting thing is if I just unselect PlanClasses, it will have same error at the class right after PlanClasses. Is there size limitation when doing import at once?
Does Access2000 upsizing wizard support migration of tables to Sql 2000?
View 1 Replies View RelatedGreetings!
I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Nothing helps.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
Thanks!
Vincent JS
I have an application written in Access 97 that connects to a SQL2000backend. One field is a description field that is a data type NTEXT in theSQL database. In my access form, I can not enter more than 255 characters.Before I converted the backend to SQL, the description field was a memofield in Access.What do I need to do to make it so I can enter more text into this field?
View 1 Replies View RelatedI was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts
EXEC sp_addlinkedserver
@server='REMOTE_OFFICE',
@srvproduct='Jet 4.0',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='F:RealEstate_Office1.mdb'
and
EXEC sp_addlinkedsrvlogin
@rmtsrvname='REMOTE_OFFICE',
@useself='false',
@locallogin='sa',
@rmtuser='Admin',
@rmtpassword=NULL
And while querying the linked server from the query analyzer using the following select command
SELECT *
FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK
I got the following error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
could you explain why this happen
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
I have developed a database on my local machine using MSDE. I am using Visual Studio.net to add tables/stored procedures to the database. I have created asp.net pages. I need to get this database on a web server that runs Enterprise SQL Server. Can I just use the 'sp_detach_db' and 'sp_attach_db' to move the database between servers?
View 4 Replies View RelatedHi Everyone,
I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0 to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps.
Can any one help me out..............
I have about 160 jobs so I cannot make new steps for each and every job.
Thanks in advance.........
Regards,
Madhuker.
Hi Everyone,
I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0(All tables from MSDB Database, sysjobs, syshistory etc) to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps.
Can any one help me out..............
I have about 160 jobs so I cannot make new steps for each and every job.
Thanks in advance.........
Regards,
Madhuker.
I want to do a data transfer but i am not sure how ..from oracle to sql server.
i want to know if its required to install the oracle client to configure so as to transfer data or not.
I heard from someone that if we change the win.ini file entry for oracle.ini and place the tnsnames.ora file i spcified location it will work but it does not what is the way to deal with..
How do i perform this with the odbc configuration for oracle..and use the same in the dts.
Thanks in advance.
Hi Every one,
I am upgrading from one computer to another.
I have few questions.
1. Is it neccessary to run the SQL Services on Domain User Account on both machines or on 6.5 machine.
2. How to check the all User logins exists in Master database.(I read in BOL that all user logins must exists in Master Database)
3. How much time it will take to complete for 10 GB database.
Please give me anohter suggetion which will help in this regard.
Thanks,
Dindu.
Can any one tell me the steps to migrate sql server 7.0 from one server to another server(Production databases). Its a 24x7 shop. The down time should be minimal. Iam looking for a most efficient way of doing that.
View 1 Replies View RelatedIs it possible to migrate SQL Server 6.5 to 2000? We have an ACtive/Active Cluster Environment. It it's possible, please give me a brief guidance on how to go about it. Thanks for your help.
View 1 Replies View Relatedhas anyone migrated a ms sql 6.5 server to a mssql server 7.0
on 2 different machines? and if so how the heck didja do it?
the 7.0 server keeps telling me that it cant do it until the 6.5 server
is upgraded.....help...help....help....the 7.0 server has the 6.5 one registered it just wont connect to it....tells me that it has to be a 7.0 server.....help
thanks
tony
What are the differneces between using Backup/Restore vs Copy Database Wizard when Migrating a SQL Server 7.0 Database to SQL Server 2000 ?
View 2 Replies View RelatedI have to migrate INFORMIX database to SQL Server 7.0 and I was wondering if there is anyone out there with any experience? I want to know the procedures and how to go about it towards a successfully INFORMIX migration to SQL Server. I look forward to hearing from you.
Regards,
Hi,
Does any one aware of any tool for sybase to ms-sql migration, which will migrate store procedures/triggers from sybase to ms-sql server 2000.
Thanks
Vaishali
Dear Experts
Can any one please help me in MIGRATION of table from Foxpro 2.5 and MSVisualFoxpro 6 to MSSqlServer 2000
I had a large no of table in foxpro now i want all of them in SQL SERVER 2000
Thanks and Regards
Hi There,
Greetings! Many of my clients are on SQL 7.0 (Win NT) and want to upgrade to SQL2K (Win 2003). We have approximately 1200 Stored Procedures (SPs) per module.
Our porting exercise involves changing keywords of SQL 7 to SQL2K (eg. LTRIM, RTRIM, GROUP BY etc).
Is there a tool available online which can run thru the SPs and do the porting job? Otherwise I have to hand-code these changes
Also, has any one out there done this exercise before? What are the things to lookout for to avoid goofups?
Thanks in advance
Hi evrybody,
I have been trying to migrate MY SQL to MSSQL and somehow cant do it.
I tried making Dsn for both of them, but the DTS wizard doesnt recognize MYSQL .
DOes anybody knwo how to do this?
thanks
We have a SQL 7 server SP3 which we want to replace. The initial planis to create a new SQL 7 server and restore the database backups.My first question is there any need to restore the master databaseonce all the other db's are copied.We had planned to copy the databases and the msdb.Do all the DTS jobs, users, security etc come over in the msdb.Also is there any reason why we shouldn't upgrade to SQL 2000, arethere any compatiability issues with restoring the SQL 7 databasebackups to the SQL 2000 server?Ed
View 1 Replies View Related
Hi,
How to migrate scheduled jobs from 2000 to 2005. Is this possible by doing bcp out and bcp in system tables which stores sql agent jobs from msdb database ?
Regards
Nit
Hi,
In DTS 2000 package dynamic property task an ini file is read and values for some keys are read. These values are nothing but name of servers on which to run some sql tasks. This dts package has three defined connections. So when the keys are read it will be either one of these three.
For eg. there are three sql tasks. sql1, sql 2 and sql3. sql1 needs to be run against server s1, sql2 against s3 and sql3 aginst s2. So the ini file sets three different variables connection1 to s1,connection2 to s3 and connection3 to s2. These variables are then used to run sql tasks accordingly.
When I migrated this dts package to .dtsx format and edited using SSIS, it gave me a blank format of vb script which we need to code. please help me out in this issue ?
Thanks,
Nit