July 2, 2009 at 11:50 am
I am having some difficulty with converting (moving) my Access 07 queries to SQL Server 2005. Is there a way you can link the queries similar to the way you link a table from Access 07 to SQL Server?
Am I able to export the queries?
Is it possible to cut and paste them in a stored procedure?
I have an additional question as well. If I cut and paste the query from Access to SQL and I run the query in SQL shouldn't the action take place in Access?
July 6, 2009 at 6:16 am
"I am having some difficulty with converting (moving) my Access 07 queries to SQL Server 2005. Is there a way you can link the queries similar to the way you link a table from Access 07 to SQL Server?"
No you can't link queries in Access 07 and SQL Server.
"Am I able to export the queries?"
If your Access database queries were developed using ANSI-92 standards (Tools -> Options ->SQl ServerCompatible mode) then you would be able to use upgrade wizard from Access to SQL Server and the wizard does it all for you.
"Is it possible to cut and paste them in a stored procedure?"
I am affraid if you haven't developed access queries using ANSI-92 standards then you will not be able to do so.
"I have an additional question as well. If I cut and paste the query from Access to SQL and I run the query in SQL shouldn't the action take place in Access?"
you cant cut and paste queries from access to sql server.
to summarise, you will not be able to move queries from access to sql server if you havent developed them using ANSI-92 standards.
Hope this helps!!!!
Regards,
Vivek
Vivek Shukla - MCTS SQL Server 2008
July 7, 2009 at 11:45 am
What the previous poster says is not entirely accurate. The answer is, it depends. In many cases, an Access query will need some level of modification before you can run it against a SQL database, but the key here is whether you intend to link to the tables from within Access, or you intend to run the query from SSMS (SQL Server Management Studio). Within Access, how you run your queries also makes a difference. If you have a customized form with unbound controls, and are doing your queries using ADO code in VBA, then you need to follow T-SQL syntax for those queries, which for many simple queries, just means replacing double quotes (") with single quotes ('). There are some queries where that won't be sufficient, and the best way to figure it out is to paste it from Access into SSMS, and then find out what error you get, look it up on Books Online, and determine how to make a change that will work and still provide the same results.
If your queries will run against linked tables, then Access's format for queries works just fine. You may want to investigate Microsoft's "SQL Server Migration Assistant". It's free, and far superior to the upsizing wizard within Access, and gives you a lot more control. Plus, you CAN migrate your queries up to SQL Server, and they'll become stored procedures there.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 7, 2009 at 1:19 pm
I've got a couple of simple queries to run (update, delete, etc...), now I moving forward with creating queries to update tables, delete tables, etc.. Is it possible to create a query that runs several queries or is it best to use a stored procedure?
July 7, 2009 at 1:30 pm
Again, it depends. Are you planning on running those stored procedures from within Access? I haven't tried migrating a query to a stored procedure as yet using SSMA, so I'm not sure if there's any kind of direct link. However, if there is, you can probably reference that stored procedure from within Access as long as it returns a recordset (even if it's an empty one). If you're going to run the procedure within SSMS, then you just need to become familiar with the EXEC command within T-SQL, which is also what you would use within any ADO-based VBA code.
Steve
(aka smunson)
:-):-):-)
jrw39 (7/7/2009)
I've got a couple of simple queries to run (update, delete, etc...), now I moving forward with creating queries to update tables, delete tables, etc.. Is it possible to create a query that runs several queries or is it best to use a stored procedure?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply