Access 2007 and connecting to SQL server 2008
in [General]
|
Prev: Cannot run function from autoexec macro
Next: dd
From: David Pierce on 1 Jan 2010 08:49 Hi all, I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro on the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam to connect to my SQL server from access. Now, I have tried to Upsize with Access (option 1 - Creat a new DB and this goes to select server, then name the DB, and click OK, well, this then specifies a message that upsizing wizzard does not work with version of SQL Server, contact MS for info and updates) Same message with SQL 2005! (Option 2 - When I use existing DB, I have to determine a file data source and DNS which I have not done as I dont know how to do this). I can however migrate any Access file to my SQL server 2008 by using the import and export 32, that works just fine but I still cannot access my database so that is mostly worthless. I have also tried upsizing a new db and existing db and neither one work. I have opend port 1433 and all tcp/ip connections have been enabeled. I have read and read and read more stuff that does nothing to resolve this problem than I can stand and still cannot connect by db to sql server. Ok, so here we are and I need a solution, any solution that works at this point is good for me. MS offers these products and recomends you buy them but does not develope them to work togeither? Why on earth is that? In any event, any help on the matter would be helpful. I am new to SQL server and have order several books but I dcouldnt find any on how to connect access 2007 to it so until someone writes that book its not available. Dont get me wrong, I love MS and there products, I also like the sql server and designing tables in that to work with my db, but I need to be able to connect to it or I just spent 5 grand on nothing! PLEASE HELP!!!!! -- If your server / PC doesnt contain the word "Supermicro" then you have no idea what you are missing!
From: David Pierce on 1 Jan 2010 16:46 OK Gang, Heres an update on SQL and Access I have been involved in extremely painstaking task today and I have now got Access 2007 to upsize to SQL Server Express and SQL Server 2005. I have come ot the conclusion this is a SQL server 2008 problem with Access 2007. I am hoping a update or hot fix will arrive shortly to allow me to use the software I purchased and hope it works. Please let me know if you have any other information or ideas as I would love ot use SQL 2008. Thank you all! David Pierce -- If your server / PC doesnt contain the word "Supermicro" then you have no idea what you are missing! "David Pierce" wrote: > Hi all, > I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro on > the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam to > connect to my SQL server from access. Now, I have tried to Upsize with Access > (option 1 - Creat a new DB and this goes to select server, then name the DB, > and click OK, well, this then specifies a message that upsizing wizzard does > not work with version of SQL Server, contact MS for info and updates) Same > message with SQL 2005! (Option 2 - When I use existing DB, I have to > determine a file data source and DNS which I have not done as I dont know how > to do this). > > I can however migrate any Access file to my SQL server 2008 by using the > import and export 32, that works just fine but I still cannot access my > database so that is mostly worthless. > > I have also tried upsizing a new db and existing db and neither one work. I > have opend port 1433 and all tcp/ip connections have been enabeled. I have > read and read and read more stuff that does nothing to resolve this problem > than I can stand and still cannot connect by db to sql server. > > Ok, so here we are and I need a solution, any solution that works at this > point is good for me. MS offers these products and recomends you buy them but > does not develope them to work togeither? Why on earth is that? In any event, > any help on the matter would be helpful. I am new to SQL server and have > order several books but I dcouldnt find any on how to connect access 2007 to > it so until someone writes that book its not available. Dont get me wrong, I > love MS and there products, I also like the sql server and designing tables > in that to work with my db, but I need to be able to connect to it or I just > spent 5 grand on nothing! > > PLEASE HELP!!!!! > > -- > If your server / PC doesnt contain the word "Supermicro" then you have no > idea what you are missing!
From: Steve on 1 Jan 2010 17:19 Hello David, See if the following helps ....... http://community.devexpress.com/blogs/paulk/archive/2009/05/20/upsizing-an-ms-access-2007-database-to-sql-server.aspx Steve santus(a)penn.com "David Pierce" <dp.david.pierce(a)gmail.com> wrote in message news:2565BD97-54A2-423D-8721-717A00805019(a)microsoft.com... > Hi all, > I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro > on > the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam > to > connect to my SQL server from access. Now, I have tried to Upsize with > Access > (option 1 - Creat a new DB and this goes to select server, then name the > DB, > and click OK, well, this then specifies a message that upsizing wizzard > does > not work with version of SQL Server, contact MS for info and updates) Same > message with SQL 2005! (Option 2 - When I use existing DB, I have to > determine a file data source and DNS which I have not done as I dont know > how > to do this). > > I can however migrate any Access file to my SQL server 2008 by using the > import and export 32, that works just fine but I still cannot access my > database so that is mostly worthless. > > I have also tried upsizing a new db and existing db and neither one work. > I > have opend port 1433 and all tcp/ip connections have been enabeled. I have > read and read and read more stuff that does nothing to resolve this > problem > than I can stand and still cannot connect by db to sql server. > > Ok, so here we are and I need a solution, any solution that works at this > point is good for me. MS offers these products and recomends you buy them > but > does not develope them to work togeither? Why on earth is that? In any > event, > any help on the matter would be helpful. I am new to SQL server and have > order several books but I dcouldnt find any on how to connect access 2007 > to > it so until someone writes that book its not available. Dont get me wrong, > I > love MS and there products, I also like the sql server and designing > tables > in that to work with my db, but I need to be able to connect to it or I > just > spent 5 grand on nothing! > > PLEASE HELP!!!!! > > -- > If your server / PC doesnt contain the word "Supermicro" then you have no > idea what you are missing!
From: Sylvain Lafontaine on 1 Jan 2010 18:32 It was a painstaking task because you have made more whining in your various posts than giving useful explanations about your situation. For example, after reading your posts, I still didn't knew if the SQL-Server instance(s) was/were located on the same or on a different machine and if you could connect to them or not by using the SQL-Server Management Studio (SSMS) tool. This information is only a starting point because later, we must have to deal with the choice of using between a windows credential or a SQL-Server credential, the possibility of having either a default instance and/or one or multiple named instances, knowing if the SQL-Server browsing service is running or not and the possibility of having one or more firewall blocking one or more required tcp-ip ports. Upsizing and Connecting are two different problems. When your connection is properly configured, any version of Access, including the very old Access 95 and 97, can connect to any version of SQL-Server, including the latest SQL-Server 2008. Upsizing is another thing because it's not only about connecting to sql-server but also about having an automated tool that will recreate the schema (design) of your tables on sql-server, copy over the data, restore the relationships and change the Access database file so that the regular tables are replaced with linked tables. This is a more complex task and actually, the tool that came with Access 2007 cannot do this against SQL-Server 2008. However, this doesn't forbid you to use Access 2007 against SQL-Server 2008 but you'll have to do the upsizing process manually or copy the database from SQL-2005 to 2008 or use another tool like the SQL Server Migration Assistant 2008 for Access V4.0 that you'll find on http://www.microsoft.com/downloads/details.aspx?FamilyId=133B59C2-C89C-4641-BEBB-6D04476EC1BA&displaylang=en For the DSN, Access is a 32 bit application so it cannot access any DSN that have been created in the 64bit registries. For this, you must use the 32 bit DSN administration tool that you will find on windows\syswow64\odbccad32.exe. (Don't ask me why this repertory is called syswow64 instead of syswow32.). Notice that you could also create and use DSN-less ODBC connections in Access but I think that the automated tool can only creates DSN-full ODBC connection. This is a limitation of the tool, not of Access. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "David Pierce" <dp.david.pierce(a)gmail.com> wrote in message news:F3DCA171-6000-4905-9FD4-E95D0BF630A1(a)microsoft.com... > OK Gang, > Heres an update on SQL and Access > > I have been involved in extremely painstaking task today and I have now > got > Access 2007 to upsize to SQL Server Express and SQL Server 2005. I have > come > ot the conclusion this is a SQL server 2008 problem with Access 2007. I am > hoping a update or hot fix will arrive shortly to allow me to use the > software I purchased and hope it works. Please let me know if you have any > other information or ideas as I would love ot use SQL 2008. Thank you all! > David Pierce > -- > If your server / PC doesnt contain the word "Supermicro" then you have no > idea what you are missing! > > > "David Pierce" wrote: > >> Hi all, >> I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro >> on >> the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam >> to >> connect to my SQL server from access. Now, I have tried to Upsize with >> Access >> (option 1 - Creat a new DB and this goes to select server, then name the >> DB, >> and click OK, well, this then specifies a message that upsizing wizzard >> does >> not work with version of SQL Server, contact MS for info and updates) >> Same >> message with SQL 2005! (Option 2 - When I use existing DB, I have to >> determine a file data source and DNS which I have not done as I dont know >> how >> to do this). >> >> I can however migrate any Access file to my SQL server 2008 by using the >> import and export 32, that works just fine but I still cannot access my >> database so that is mostly worthless. >> >> I have also tried upsizing a new db and existing db and neither one work. >> I >> have opend port 1433 and all tcp/ip connections have been enabeled. I >> have >> read and read and read more stuff that does nothing to resolve this >> problem >> than I can stand and still cannot connect by db to sql server. >> >> Ok, so here we are and I need a solution, any solution that works at this >> point is good for me. MS offers these products and recomends you buy them >> but >> does not develope them to work togeither? Why on earth is that? In any >> event, >> any help on the matter would be helpful. I am new to SQL server and have >> order several books but I dcouldnt find any on how to connect access 2007 >> to >> it so until someone writes that book its not available. Dont get me >> wrong, I >> love MS and there products, I also like the sql server and designing >> tables >> in that to work with my db, but I need to be able to connect to it or I >> just >> spent 5 grand on nothing! >> >> PLEASE HELP!!!!! >> >> -- >> If your server / PC doesnt contain the word "Supermicro" then you have no >> idea what you are missing!
From: David Pierce on 7 Jan 2010 09:43
If you had read the first line of my post instead of worrying about what I was whining about, you would have read these are all on the same machine, Thank you! Also, the migration does work to bring tables to SQL Server just fine, but if you cannot connect them in your access db, then what good is that I ask? -- If your server / PC doesnt contain the word "Supermicro" then you have no idea what you are missing! "Sylvain Lafontaine" wrote: > It was a painstaking task because you have made more whining in your various > posts than giving useful explanations about your situation. For example, > after reading your posts, I still didn't knew if the SQL-Server instance(s) > was/were located on the same or on a different machine and if you could > connect to them or not by using the SQL-Server Management Studio (SSMS) > tool. > > This information is only a starting point because later, we must have to > deal with the choice of using between a windows credential or a SQL-Server > credential, the possibility of having either a default instance and/or one > or multiple named instances, knowing if the SQL-Server browsing service is > running or not and the possibility of having one or more firewall blocking > one or more required tcp-ip ports. > > Upsizing and Connecting are two different problems. When your connection is > properly configured, any version of Access, including the very old Access 95 > and 97, can connect to any version of SQL-Server, including the latest > SQL-Server 2008. > > Upsizing is another thing because it's not only about connecting to > sql-server but also about having an automated tool that will recreate the > schema (design) of your tables on sql-server, copy over the data, restore > the relationships and change the Access database file so that the regular > tables are replaced with linked tables. This is a more complex task and > actually, the tool that came with Access 2007 cannot do this against > SQL-Server 2008. > > However, this doesn't forbid you to use Access 2007 against SQL-Server 2008 > but you'll have to do the upsizing process manually or copy the database > from SQL-2005 to 2008 or use another tool like the SQL Server Migration > Assistant 2008 for Access V4.0 that you'll find on > http://www.microsoft.com/downloads/details.aspx?FamilyId=133B59C2-C89C-4641-BEBB-6D04476EC1BA&displaylang=en > > For the DSN, Access is a 32 bit application so it cannot access any DSN that > have been created in the 64bit registries. For this, you must use the 32 > bit DSN administration tool that you will find on > windows\syswow64\odbccad32.exe. (Don't ask me why this repertory is called > syswow64 instead of syswow32.). Notice that you could also create and use > DSN-less ODBC connections in Access but I think that the automated tool can > only creates DSN-full ODBC connection. This is a limitation of the tool, > not of Access. > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site: http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > > "David Pierce" <dp.david.pierce(a)gmail.com> wrote in message > news:F3DCA171-6000-4905-9FD4-E95D0BF630A1(a)microsoft.com... > > OK Gang, > > Heres an update on SQL and Access > > > > I have been involved in extremely painstaking task today and I have now > > got > > Access 2007 to upsize to SQL Server Express and SQL Server 2005. I have > > come > > ot the conclusion this is a SQL server 2008 problem with Access 2007. I am > > hoping a update or hot fix will arrive shortly to allow me to use the > > software I purchased and hope it works. Please let me know if you have any > > other information or ideas as I would love ot use SQL 2008. Thank you all! > > David Pierce > > -- > > If your server / PC doesnt contain the word "Supermicro" then you have no > > idea what you are missing! > > > > > > "David Pierce" wrote: > > > >> Hi all, > >> I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro > >> on > >> the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam > >> to > >> connect to my SQL server from access. Now, I have tried to Upsize with > >> Access > >> (option 1 - Creat a new DB and this goes to select server, then name the > >> DB, > >> and click OK, well, this then specifies a message that upsizing wizzard > >> does > >> not work with version of SQL Server, contact MS for info and updates) > >> Same > >> message with SQL 2005! (Option 2 - When I use existing DB, I have to > >> determine a file data source and DNS which I have not done as I dont know > >> how > >> to do this). > >> > >> I can however migrate any Access file to my SQL server 2008 by using the > >> import and export 32, that works just fine but I still cannot access my > >> database so that is mostly worthless. > >> > >> I have also tried upsizing a new db and existing db and neither one work. > >> I > >> have opend port 1433 and all tcp/ip connections have been enabeled. I > >> have > >> read and read and read more stuff that does nothing to resolve this > >> problem > >> than I can stand and still cannot connect by db to sql server. > >> > >> Ok, so here we are and I need a solution, any solution that works at this > >> point is good for me. MS offers these products and recomends you buy them > >> but > >> does not develope them to work togeither? Why on earth is that? In any > >> event, > >> any help on the matter would be helpful. I am new to SQL server and have > >> order several books but I dcouldnt find any on how to connect access 2007 > >> to > >> it so until someone writes that book its not available. Dont get me > >> wrong, I > >> love MS and there products, I also like the sql server and designing > >> tables > >> in that to work with my db, but I need to be able to connect to it or I > >> just > >> spent 5 grand on nothing! > >> > >> PLEASE HELP!!!!! > >> > >> -- > >> If your server / PC doesnt contain the word "Supermicro" then you have no > >> idea what you are missing! > > > . > |