Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:
> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>> I don't know where to begin with this. We designed an online product
>> catalogue who now want to put it on a CD. Is there any third party piece
>> of
>> software that could convert an sql server or access db into standalone
>> program that the client could put on a CD without he need for Access
>> Forms,
>> OLEDB etc.
>> If I could put the db in XML could I point the Access runtime, which the
>> developer version of Office can export, at that as a datasource without
>> the
>> need for OLEDB drivers.
>>

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.
Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>
|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece o
f
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms
,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without th
e
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>

Offiline synchro

Hi everyone,

I am working on a web site / back end for a food delivery service in the UK. I have designed a web site using ASP and a MS SQL server and am now designing the back end for office use in MS Access. I have linked the SQL tables into access for the app to read directly.

My client has informed me that he wants the back end to function without internet connectivity - so that he can still view existing orders if the net goes down.

I have been pondering the best way to do this and have come up with 2 solutions - either duplicate all the tables locally in access and write an import routine to copy data periodically, or set up a copy of SQL server in the office to synchronise the data independently and then tell the Access prog to read from it if it cannot access the web.

I would prefer the latter option as its neater and requires less programming, but I have never done this before (or know if its even possible). Does anyone have any opinion on how this problem would be best overcome, and if the latter solution is viable then how I would go about it?

All help is much appreciated, thank you for reading.

MarkYes, this is possible and usually it is easy.

Microsoft SQL Replication will definitely do what you want, but the exact deails will depend on which problems you want it to solve for you and which ones you want to solve for yourself. Without knowing more, I can't give you specifics.

I've got some strong opinions about ways that I'd set this up, but those are based on which problems suit my style... My users and I prefer dealing with nusiance grade problems every week or so as opposed to show-stoppers every year or so, and I don't know of any way to set up two different databases (which are both editable) that won't occaisionally have conflicts.

-PatP

Odd SqlException

I'm using .NET 2.0

I designed my BLL to work as follows:

'Update existing company <System.ComponentModel.DataObjectMethodAttribute _ (System.ComponentModel.DataObjectMethodType.Update,True)> _Public Function UpdateCompany(ByVal Original_CompanyIDAs Int64, ...)As Boolean'Get current recordDim CompaniesAs biodata.CompaniesDataTable = CompaniesAdapter.GetCompanyByID(Original_CompanyID)'If no record exists, Update failsIf Companies.Count = 0Then Throw New ApplicationException("Company does not exist.")End If Dim CompanyAs biodata.CompaniesRow = Companies(0)'Set all the values to load into the database Company.CompanyName = CompanyName ...Dim RowsAffectedAs Integer = CompaniesAdapter.Update(Company)Return (RowsAffected = 1)End Function

Recently I deleted a column in my SQL database. In order to account for this change, I deleted the same column in my DAL. Then, because of the obvious compile errors that would occur if I didn't, I deleted all references to that column in my BLL. My DAL automatically creates the Update and Insert statements. Now, the error I'm getting is as follows:

Exception Details:System.Data.SqlClient.SqlException: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The statement has been terminated.

Source Error:

Line 18409: System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")> _Line 18410: Public Overloads Overridable Function Update(ByVal dataRow As System.Data.DataRow) As IntegerLine 18411: Return Me.Adapter.Update(New System.Data.DataRow() {dataRow})Line 18412: End FunctionLine 18413:

Stack Trace:

[SqlException (0x80131904): Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.The statement has been terminated.] System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +200 System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +104 System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +3042 System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows) +345 biodataTableAdapters.CompaniesTableAdapter.Update(DataRow dataRow) in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\67b7db20\aea00f88\App_Code.nf1apfb7.1.vb:18411 CompaniesBLL.UpdateCompany(Int64 Original_CompanyID, String CompanyName, String Division, String Website, Nullable`1 CompanyInactive, String CompanyInactiveReason, Nullable`1 EmployeeCount, Nullable`1 YearFounded, Nullable`1 YearStartedBiobased, Nullable`1 NAICS, String Address1, String Address2, String City, String StateCode, String Territory, String CountryCode, String Zip, String CompanyNotes, String Prefix, String FName, String LName, String Suffix, String Title, String Email, String Phone1, String PhoneType1, String Phone2, String PhoneType2, String Phone3, String PhoneType3, String UpdateType, String UpdatedBy, Int64 SecurityLevel, Int64[] Flags) in F:\App_Code\BLL\CompaniesBLL.vb:376

Now, this exception makes no sense to me because the DAL should automatically fix the update and insert statements itself, and I never directly access those statements. The error is occuring at CompaniesAdapter.Update(Company) in my code above. Any ideas?

Thanks,
BSY E-718

Turn on SQL Profiler... and watch the actual query that is being executed... that might point you in the right direction.

ODBC over VPN

We have designed a thick client accounting system using Microsoft Access 200
3.
The thick clients connect to MS SQL Server 2000 or 2005 via ODBC.
This works well in a LAN environment. We have customers that want us to
connect their many branches together now. These branch offices are located i
n
different geographical regions, which could stretched over 100 to 200km apar
t.
We know that having these systems designed in Web would solve our problems,
but since we do not have much time and the budget to convert all these
systems from Access 2003 to web, we thought that the fastest way out is to
connect the thick clients (located in these branch offices) to the Head
Quarters (which has the centralised MS SQL server databases) is still using
ODBC over a high speed broadband from between 1 MBps to 3 MBps.
To increase security, we though of creating different VPNs connect each
branch to the HQ, and have all these branch systems linked via ODBC over VPN
to the HQ.
Please advise me whether the above concept is alright or not, in terms of:
1. whether such networking will work in the first place? any need of doing
further software modification?
2. whether using VPN would solve security problems during data transfer
between offices?
3. whether the existing broadband speeds, as mentioned above, is sufficient
to do the job?
thank you all in advance
JOSEPH YOONGJoseph,
I think you'll find that ODBC over a VPN connection to have very poor
performance. The overhead of the VPN, combined with the overhead at the
application layer is a bad recipe for client/server connectivity. I've
seen this with a couple different OBDC/SQL based applications. The
bottom line is almost always to either port the application to something
web-based or to use Terminal Services with or without Citrix.
Greg
*** Sent via Developersdex http://www.codecomments.com ***|||"Greg Lara" wrote:

> Joseph,
> I think you'll find that ODBC over a VPN connection to have very poor
> performance. The overhead of the VPN, combined with the overhead at the
> application layer is a bad recipe for client/server connectivity. I've
> seen this with a couple different OBDC/SQL based applications. The
> bottom line is almost always to either port the application to something
> web-based or to use Terminal Services with or without Citrix.
> Greg
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
Hi Greg
Thanks a million for the reply.
I understand what you mean.
In my country, I have tested this ODBC over VPN with a 2MB fibre optic
broadband speed and it works OK for now.
This is only 1 client connected to 1 server. The distance between the client
and server is about 2km away. Not sure how this will fair if we have the
following scenarios:
1. distances between 50km to 500km or
2. clients between 5 to 100 per server?
I am not sure whether 2MB (upload and download speed will be sufficient for
now)
Web-based solution will definitely solve my situation here, I know. But I am
not willing to spend another 2 years for a major re-write. I have taken abou
t
6 years already to complete this software, when web based solutions were
still in their infancy stage. .NET technologies were not yet available then.
Is there a quick way of transferring my MS Access 2003 windows based thick
client to .NET 2 web based soluton? if yes, do let me know. I don't mind
spending some money (not time) to do the conversion.
Otherwise, please elaborate how terminal services can work. What I know
about terminal services is that everything is done by the server. The client
PC merely acts as a "dummy" terminal, only transferring key strokes to the
connected server. How can this improve the performance of client / server?
I have heard of Citrix before, but frankly not sure what this is. Could you
please help me understand.
Thanks
regards
joseph yoong