|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
read/write excel using odbchow can i write to an excel .xls file using odbc?
i've read in several places that its possible but i can't find an example. i have managed to read an excel file using odbc A couple of things:
1. Try to avoid ODBC, and use OleDB if you can. Google "IDataReader Excel" and you'll find connection strings for that. 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out. This will help determine if your connection string is off, or you're ONLY having an insert issue. 3. I believe (long term memory working here) there is something about a column having to have a "primary key" on it (yes, I"m talking about excel) before you can do update/insert commands on an excel spreadsheet. You'll have to search, I don't know any links for key phrases on that one. ... Please post your resolution when you're done so other can learn from the experience. Show quote ".paul." <p***@discussions.microsoft.com> wrote in message news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com... > how can i write to an excel .xls file using odbc? > i've read in several places that its possible but i can't find an example. > > i have managed to read an excel file using odbc there was a problem with the connection string.
i changed it to this and it worked: Dim ConnectionString As String = "Driver={Microsoft Excel Driver (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" & Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString & "\joblog.xls;" Show quote "sloan" wrote: > > A couple of things: > > 1. Try to avoid ODBC, and use OleDB if you can. > Google "IDataReader Excel" and you'll find connection strings for that. > > 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find out. > This will help determine if your connection string is off, or you're ONLY > having an insert issue. > > 3. I believe (long term memory working here) there is something about a > column having to have a "primary key" on it (yes, I"m talking about excel) > before you can do update/insert commands on an excel spreadsheet. > You'll have to search, I don't know any links for key phrases on that one. > > ... > > Please post your resolution when you're done so other can learn from the > experience. > > > > > > ".paul." <p***@discussions.microsoft.com> wrote in message > news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com... > > how can i write to an excel .xls file using odbc? > > i've read in several places that its possible but i can't find an example. > > > > i have managed to read an excel file using odbc > > > Which is why I asked.
Getting Excel connection strings is ..... very very tricky sometimes. ; space quotes, they'll screw you. .................. You still may want to look at the OleDB provider, there is no reason to use ODBC anymore. http://support.microsoft.com/kb/316934 (except dont hack together a presentation/datalayer spaghetti like they did on the sample) Show quote ".paul." <p***@discussions.microsoft.com> wrote in message news:A8BABABA-DE62-435A-B5E9-D44939A42473@microsoft.com... > there was a problem with the connection string. > i changed it to this and it worked: > > Dim ConnectionString As String = "Driver={Microsoft Excel Driver > (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" & > Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString > & "\joblog.xls;" > > > "sloan" wrote: > >> >> A couple of things: >> >> 1. Try to avoid ODBC, and use OleDB if you can. >> Google "IDataReader Excel" and you'll find connection strings for that. >> >> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find >> out. >> This will help determine if your connection string is off, or you're ONLY >> having an insert issue. >> >> 3. I believe (long term memory working here) there is something about a >> column having to have a "primary key" on it (yes, I"m talking about >> excel) >> before you can do update/insert commands on an excel spreadsheet. >> You'll have to search, I don't know any links for key phrases on that >> one. >> >> ... >> >> Please post your resolution when you're done so other can learn from the >> experience. >> >> >> >> >> >> ".paul." <p***@discussions.microsoft.com> wrote in message >> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com... >> > how can i write to an excel .xls file using odbc? >> > i've read in several places that its possible but i can't find an >> > example. >> > >> > i have managed to read an excel file using odbc >> >> >> > You still may want to look at the OleDB provider, there is no reason to That's pretty extreme. If you happen to need to connect to the largest > use ODBC anymore. number of different databases ODBC is still hugely valuable. While all the major DBs support OleDB these days, there are a number I've needed to read from that only had ODBC. Of course it's no big deal simple use DBConnection, DBReader, etc and instantiate the appropriate class Show quote "sloan" <sl***@ipass.net> wrote in message news:OmU%23$vXFIHA.2004@TK2MSFTNGP06.phx.gbl... > > > Which is why I asked. > > Getting Excel connection strings is ..... very very tricky sometimes. > > ; space quotes, they'll screw you. > > ................. > > You still may want to look at the OleDB provider, there is no reason to > use ODBC anymore. > > http://support.microsoft.com/kb/316934 > (except dont hack together a presentation/datalayer spaghetti like they > did on the sample) > > > > > > ".paul." <p***@discussions.microsoft.com> wrote in message > news:A8BABABA-DE62-435A-B5E9-D44939A42473@microsoft.com... >> there was a problem with the connection string. >> i changed it to this and it worked: >> >> Dim ConnectionString As String = "Driver={Microsoft Excel Driver >> (*.xls)};READONLY=FALSE;DriverId=790;Dbq=" & >> Directory.GetParent(Directory.GetParent(My.Application.Info.DirectoryPath).ToString).ToString >> & "\joblog.xls;" >> >> >> "sloan" wrote: >> >>> >>> A couple of things: >>> >>> 1. Try to avoid ODBC, and use OleDB if you can. >>> Google "IDataReader Excel" and you'll find connection strings for that. >>> >>> 2. Can you read the data? ( Do a "Select * from [Sheet1$] " and find >>> out. >>> This will help determine if your connection string is off, or you're >>> ONLY >>> having an insert issue. >>> >>> 3. I believe (long term memory working here) there is something about >>> a >>> column having to have a "primary key" on it (yes, I"m talking about >>> excel) >>> before you can do update/insert commands on an excel spreadsheet. >>> You'll have to search, I don't know any links for key phrases on that >>> one. >>> >>> ... >>> >>> Please post your resolution when you're done so other can learn from >>> the >>> experience. >>> >>> >>> >>> >>> >>> ".paul." <p***@discussions.microsoft.com> wrote in message >>> news:15F5DE3A-73AC-4913-ACDE-32D8722D91C6@microsoft.com... >>> > how can i write to an excel .xls file using odbc? >>> > i've read in several places that its possible but i can't find an >>> > example. >>> > >>> > i have managed to read an excel file using odbc >>> >>> >>> > > On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust"
<and***@andrewfaust.com> wrote: >> You still may want to look at the OleDB provider, there is no reason to But for some applications performance is a critical issue -- ODBC is>> use ODBC anymore. > >That's pretty extreme. If you happen to need to connect to the largest >number of different databases ODBC is still hugely valuable. While all the >major DBs support OleDB these days, there are a number I've needed to read >from that only had ODBC. Of course it's no big deal simple use >DBConnection, DBReader, etc and instantiate the appropriate class slower than OLEDDB Not disputing it. I totally agree you should use the other providers when
available. I was just pointing out that there are databases for which there is only ODBC, thus there is sometimes a reason to use ODBC. Show quote "Rad [Visual C# MVP]" <rad@nospam.com> wrote in message news:csavh3dp7samnc1rlhmuv6eavc0gmt7iis@4ax.com... > On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust" > <and***@andrewfaust.com> wrote: > >>> You still may want to look at the OleDB provider, there is no reason to >>> use ODBC anymore. >> >>That's pretty extreme. If you happen to need to connect to the largest >>number of different databases ODBC is still hugely valuable. While all >>the >>major DBs support OleDB these days, there are a number I've needed to >>read >>from that only had ODBC. Of course it's no big deal simple use >>DBConnection, DBReader, etc and instantiate the appropriate class > > But for some applications performance is a critical issue -- ODBC is > slower than OLEDDB > > -- > http://bytes.thinkersroom.com Gotcha.
I should have had "With Excel" at the end of it to avoid the ambuguity. Show quote "Andrew Faust" <and***@andrewfaust.com> wrote in message news:99312F0D-3F09-467D-BEBF-DF526AB0F064@microsoft.com... > Not disputing it. I totally agree you should use the other providers when > available. I was just pointing out that there are databases for which > there is only ODBC, thus there is sometimes a reason to use ODBC. > > -- > Andrew Faust > andrew[at]andrewfaust.com > http://www.andrewfaust.com > > > "Rad [Visual C# MVP]" <rad@nospam.com> wrote in message > news:csavh3dp7samnc1rlhmuv6eavc0gmt7iis@4ax.com... >> On Tue, 23 Oct 2007 19:35:40 -0600, "Andrew Faust" >> <and***@andrewfaust.com> wrote: >> >>>> You still may want to look at the OleDB provider, there is no reason to >>>> use ODBC anymore. >>> >>>That's pretty extreme. If you happen to need to connect to the largest >>>number of different databases ODBC is still hugely valuable. While all >>>the >>>major DBs support OleDB these days, there are a number I've needed to >>>read >>>from that only had ODBC. Of course it's no big deal simple use >>>DBConnection, DBReader, etc and instantiate the appropriate class >> >> But for some applications performance is a critical issue -- ODBC is >> slower than OLEDDB >> >> -- >> http://bytes.thinkersroom.com >  |
|||||||||||||||||||||||
Other interesting topics