May 5, 2014 at 1:11 pm
Hello all,
I've played around with Access and SQL statements for some time, not a master by any means, but the data I am storing has gotten to the point where we have had to move it to an SQL server (sharing the data across many offices).
At this time, the data stores information that is called up by a visio floor plan. Right now, I have an Access front end that creates the query putting information together from a couple of tables for what the Visio floor plan needs. I was thinking, would it not be better to have the SQL server run the query?
If I am on the right track, then here I am. I have created the Query (poking around in SQL Server Managment Studio), but this is not like access where there is a section for Tables, Queries, Ect. It seems that Queries are saved as project files? how would I have my Visio linked data find the query? In visio, using the connection wizard, I can see the tables...
May 5, 2014 at 1:57 pm
jdasilva (5/5/2014)
Hello all,I've played around with Access and SQL statements for some time, not a master by any means, but the data I am storing has gotten to the point where we have had to move it to an SQL server (sharing the data across many offices).
At this time, the data stores information that is called up by a visio floor plan. Right now, I have an Access front end that creates the query putting information together from a couple of tables for what the Visio floor plan needs. I was thinking, would it not be better to have the SQL server run the query?
If I am on the right track, then here I am. I have created the Query (poking around in SQL Server Managment Studio), but this is not like access where there is a section for Tables, Queries, Ect. It seems that Queries are saved as project files? how would I have my Visio linked data find the query? In visio, using the connection wizard, I can see the tables...
In Sql Server, queries are called views, tables are of course tables but then you have all the things you do in modules, classes, functions and such in Access under the programmability tab in each database in Sql Server.
Many things are similar and for certain, if you can store the data in Access, you can store it in Sql Server. Play around with it and fire us a question when (if) you get stuck
😎
May 5, 2014 at 2:42 pm
View is what I needed!
I am able to bypass the Access file (for the visio file to get the data) and it works the same for what I can see. I did have some issues with the SQL statement (it didn't seem to like my IIF statement, so I changed it to a case and it works) for a custom field in the query (was using the "wizard" gui in the SQL Studio at first, then edited the statement in the view).
I guess my next (ambisious) step is to move away from Access altogether and move it to a web app, or tie in to Share Point. They are now telling me that the data needs to be output to Share Point, but I have yet to work with that... don't worry about that for now, as I get rolling, I will post again.
Thanks for the insite!
May 5, 2014 at 2:48 pm
Good stuff!
IIF is supported in Sql Server 2012 and later, my guess to aid portability from Access:-D
Good luck
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply