Hello all,
Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7?
I've over 400 of them and I am trying not to have to do them by hand.
Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.
At work we are migrating several Access databases to SQL Server 2005 standard edition. These Access databases have no user interface forms. They do however have a series of macros that execute make table queries.
I'm trying to determine what are options are for replacing the make table queries in the macros. My first thought was to do the work in a SQL Server stored prodecure. I am asking for advice on what our options are. Is a stored procedure the way to go? Any suggestions on how to do it?
Hi- Can you give me any help/assistance on the development process involved with migration from MS Access to SQL Server 7. Specifically, I need my current MS Access data migrated to SQL, and then I need two (2) fairly simple forms developed for data input and searching on the web. The developers I have been speaking too seem to want to buy their Hawaiian condo from this one project...which seems like A LOT OF $$ for the development of an off-the-shelf product that costs $15,000. Any leads and/or thoughts would be GREATLY APPRECIATED. THANKS.
I would like to migrate an access data to SQL Server. However, the Access database has an OLE data type field that stores word documents. Is there a way to handle this in SQL Server?
Hello,We maintain a VB6 front-end application using an Access 2000 database. Allcode and forms are in VB6. The program also uses several queries/reportsdefined in Access. For corporate reasons we must move to SQL Server rightaway. Based on our configuration, we're hoping someone can give us a feelfor what's involved here. Since we're only using Access for the data we'renot sure what an "upsizing" would entail. Can someone point us in the rightdirection, tell us the main gotchas, etc. Thanks a lot.Teri Welch
I'm very new to MS SQL Server; I just downloaded Express 2005 yesterday. I have it up and running; it seems to be working fine.
Now onto business - I need to migrate data from existing MS Access 2003. How, exactly, do I do this? I've tried searching MSDN, TechNet, and here, and while I get results that seem to tell part of the story, I don't seem to get a true "How to" for this task.
I am in the process of migrating 40 access databases to SQL Server. I am migrating the tables to the backend. All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to stay seperated by each location. So I added a location field to each table in the database; so that each locations data will be seperated by location. How do I go about filtering out the the data so that each location can only update, delete, an view their own data? (I dont mind a long answer.)
got some MS Access SQL Code that needs converting into TSQL:
SELECT dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName, Sum(IIf(IsNull([CompletionDate]),0,1)) AS Completed, Count([pkServiceID])-Sum(IIf(IsNull([CompletionDate]),0,1)) AS Uncompleted, Count(dbo_MyServiceRequests.pkServiceID) AS TotalCount FROM dbo_qryMyServices LEFT JOIN dbo_MyServiceRequests ON dbo_qryMyServices.FormsServiceID = dbo_MyServiceRequests.PostType GROUP BY dbo_qryMyServices.FormsServiceID, dbo_qryMyServices.ServiceName ORDER BY dbo_qryMyServices.ServiceName;
because it's Access and got VBA stuff in it - IIF and ISNULL, I can't figure out how to make it work in proper SQL.
I was really happy to see the Migration Assitant for transferring the Access2000 data to SQL 2005. Unfortunately, during the migration wizard, when it comes to the window of "connect to SQL Server 2005" it gives an error. It says "Connection to SQL Server Failed. An error has occured while establishing connection to the server... also point out it could be SQL server not allowing remote connections (I checked), and something that says: provider: named pipes provider , error 40" ... I'm stuck at this window. Please please help? you can email me directly if you wish to fh.erdogan(at)gmail.com Thanks. p.s. I have a fresh install of SQL Server 2005. All on the same machine.
I'm using the SSMA to migrate Access databases into SQL Sever. It's a pretty nifty tool, however, I am having some trouble fully migrating the data over.
When I select 'Convert Schema' the schemas for all the tables are created on the server and *all* the table names appear in the Server Metadata Explorer. There are the usual warnings about name changes and primary key additions and the like but importantly, the output window says that there were no errors. Scrolling through all the tables on the server explorer with the 'Table' tab selected confirms that the schemas for all the tables have been migrated, but for one or two of the newly created tables, the 'Data' tab shows the error message "Failed to retrieve data: Invalid object name 'NewDB.dbo.Pref'" for example. Consequently, when I move on to migrating the data, the operation fails for the tables showing these errors and the error message 'The table 'NewDB.dbo.[Pref]' does not exist in SQL Server' is shown when it is displaying in the Metadata Explorer. When I view the server schema in Management Studio Express, it confirms that they have not been migrated. I have tried to look at the failing tables' schemas and identify any similarities which might be causing them to fail migration but they are all very different so I don't think it's a schema issue.
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.
I've downloaded the migration tool to convert an Access 2000 DB to a SQL DB. The VB code utilizes DAO (3.6) for hourly automatic compaction and the creation of an encrypted security database. ADO is used for the data maintenance functions. I expect the current connection methodology of ODBC using Jet 4.0 will need to be modified (many articles here address this). All the SQL code is inline and also must be modified. The Coldfusion code utilizes Micrsoft Access with Unicode via an ODBC connection with only SQL Select statements. At another job, I migrated an Informix 7 database to MS SQL 7 and for the most part the SQL code remained the same except for the wildcard spec from * to %.
I'm looking for insights and/or shortcuts on this effort, primarily in the SQL command mods area. I'm perusing the threads here as we speak, but hoped someone knew of a specific thread I could go to....laziness is alive and well for us chronologically gifted people, i.e. over 55.
I migrated the Northwind database using SQL Migration Assistant for Access. it worked fine and I was able to link the access forms to Local machine SQL Server 2005 and it worked fine.
Now i want to link the same Northwind access database to Another Server on the network.
When i try to run the wizard again, it won't migrate the user objects like tables to server, it will just create a database and that is it. it will give me this error too.
I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.
we want to completely do away with access, on my report tool,which is crystal report, we want query to be our data source so , we do not want to use ssis packageor import from access to sql, how can l handle bringing all table and the query from Access together.
I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).
The file is actually a backend, so nothing to migrate other than tables.
After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.
Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):
[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.
There is nothing wrong, so far as I can determine, with the relationships involved.
I can insert data into the tables using any of the following methods: 1. Directly in Access, in the source backend. 2. Using the original Access frontend application, attached to the source backend. 3. Using VB.Net forms I am developing, in the 'upsized' database. 4. Directly in the Management Studio, in the 'upsized' database.
None of these four methods complains in the least about the relationships which SSMA balks at.
I downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.* FROM tblCalls WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime FROM qryFlyTippingStatsSummary1 GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.BEYLEVEL16'.
I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.
I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.
I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.
So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:
Select Time_In_Table.Visitor_Number Not Union Select Time_Out_Table.Visitor_Number
If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.
Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function.
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription, tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%])))) AS [%], 0+round2([Amount]*[%]) AS Allocated, 0+round2([Allocated]*[Salvage%]) AS Salvage FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON ([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND ([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND ([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
Hi All, I hope I am at the right place to post this question: How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself. Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT * INTO dupContactKeys FROM Contacts WHERE ContactKey IN ( SELECT ContactKey FROM Contacts GROUP BY ContactKey HAVING COUNT(*) > 1)
SELECT * FROM dupContactKeys WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have an application where users can enter data into any (or all) of 6 search fields, to produce a filtered query.
This works fine using my Access version(see code below), but as SQLS2k cannot use "IIF", I tried to replace these bits with "CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
FROM (((tAudit LEFT JOIN tCategories ON tAudit.CategoryID = tCategories.CategoryID) LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID) LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID) LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType])) AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID])) AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])) AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID])) AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID])) AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
I have upsized some Access 2002 tables to Sql Server 2005 express. I put together a query and when I try to execute it, it tells me the MS Access is an earlier version prior to SQL server 2005 and it will not save the queries. When I try to run them anyway, I get the repeated message and the query never executes. I'm taking a T-SQL class and this is how the instructor. Is they a fix for SQL Server or Access to get around this or do I have to revert back to MDSE 2000?? Can MDSE 2000 and SQL Server be loaded onto the same machine (laptop)? Help!!!!! I am very new at this...
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton (IT Support) Durham Aged Mineworkers Homes Association
we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
