MS SQL hiding behind Access

Hi guys

We have a helpdesk application which is based on an MS SQL database, and which runs with a rather large and complicated C# based interface. We don't have the code for this, so we can't customise it. Long story short, what we want is to create an interface in Access (or similar) which accesses the same database as the helpdesk suite, and allows reading AND writing, and some rather complex operations (which SHOULD be fairly simple to do in SQL).

Unfortunately, I have been given this project, and I know about as much about Access and SQL as I do about Ghengis Khan's fashion sense.

I will follow with more information as required, but I'm going to need a lot of help with this one. First things first, is Access the way to do this? Is it going to be easier to create a new Access DB and synchronise it with the SQL, instead of both applications using the same database?

The only way I can get Access to interrogate the SQL database is to create a Data Access Page - is this the correct starting point? The only problem is that this seems to only offer HTML, and is far from being a friendly interface, at least not to me.

I know that I currently have no grounding in physics and I'm trying to build a space station, but any advice that you could give me would be much appreciated.

Thanks guys!Access is the tool if you want it done fast and you do not have a firm grounding in another programming language.

Read about Access Data Projects (.adp files).|||Hi

Agree with Thrasy thingy me bob.
You don't need to use adp but it is rather well suited for use against a SQL Server BE.
You don't need to use DAP unless you specifically want an html based interface. If you do use this you will find the forum resources at your disposal drastically reduced because they don't get used that much.

You do not want to create an Access db and synchronise with SQL Server. Either link to the server (simple) or use ADO code to create a disconnected interface (a bit complex if you are new to this but a more ideal set up).

Is this a third party product? If so then check your agreement with the supplier. You might be invalidating your agreement and they may, for example, be able to withdraw support. Also be aware that there may be some (a lot??) of business knowledge embedded in the C# FE which you will have to infer. Similarly, there are probably sprocs that enforce some validations etc for inserts and updates - you could probably do with looking at these and using them where you can.

I must say - I would be nervous about writing my own FE to an application I haven't built\ don't have access to the source code. Can you not use the C# app for inputting and limit yourself to procuding a bespoke reporting front end instead?

There is an Access forum for questions of a more access orientated nature. I think you need to prepare yourself for a very steep learning curve if you aren't familiar with Mr Kahn's clothing predelictions.|||Steep learning curves are generally the way things are done around here. My biggest problem is having to forget everything I know about Lotus Notes in order to start learning about REAL databases!!

The data access project seemed to be what I wanted - I now have an SQL database which is apparently pretending to be an Access database, so assumedly I can just forget all about SQL and work with Access forms and VB code as though this was an Access DB, is that correct?

Also, just to stop me destroying the world, is it easy to create a copy of the MS SQL DB and work with that instead, just until I know what I'm doing. If I break this database there are several peole that will be after my blood!!

In case you're wondering, the reason for this project is that the C# interface provided is fine for support staff, and works well, but unfortunately has all the customizability of a brick, and doesn't support the sales/invoicing staff very well. So we want to keep the C# interface for the support staff, but make a different one for the sales staff. Is there going to be any problem with both parties accessing the data at the same time? It is VERY unlikely that we will ever overlap on editing entities, due to the (quite) seperate nature of the two sides to the system.|||The data access project seemed to be what I wanted - I now have an SQL database which is apparently pretending to be an Access database, so assumedly I can just forget all about SQL and work with Access forms and VB code as though this was an Access DB, is that correct?Nope. I'm not too familiar with adpsbut they are really more the other way around as I understand them - they bring much of the SQL Server functionality into Access - Access becomes a sort of rudimentary SQL Server tool (as well as a UI designer).

Also, just to stop me destroying the world, is it easy to create a copy of the MS SQL DB and work with that instead, just until I know what I'm doing. If I break this database there are several peole that will be after my blood!! Oh dear lord yes do that. At the minimum have a test database where you fine tune any of your work before using it on the production database. I would suggest that using this test database indicates that you have some idea of what you are doing rather than something to dump once you can write a few lines of code. I'm not sure if it is possible to destroy the world with SQL Server though that might be one of the new features in 2005.
Gist is - you want to backup your database and then restore it (ideally on a different, dedicated server). I am afraid I use enterprise manager to create and restore backups - a bad habit I will probably be chastised for. Check out BACKUP in BoL and Google to avoid learning bad habits.

MS SQL (online) - Access(offline) system

Hi everyone,

I 've built an application which runs on a ms sql Database, now my employer want a offline database system as well. I've already implemented Access, but having difficulties with something.

I was creating a form to transfer all data from the sql database to the access database, when I experienced a problem in my first (access) data accessor. Sql gave a syntax error, when I copied the sql string with some test data to Access it obviously gave the same error but afterwards focussed on the DataTime field of my sql string. So I'm assuming the problem lies with that.

Is there a difference between the DateTime in SQL Server and Access? If so is there a way to convert from one to the other?

My SQL test string:

Code Snippet

INSERT INTO dbo_tblPerson(ID,Name,Surname,Email,Website,Address,Country,Comments,DateTime,Telephone,Mobile,Active)
VALUES (1,To,Vdb,info@.,www.,str,BE,Test,Date.Now(),0474,0474,0)

My Data Accessor:

Code Snippet

public static int addPerson(clsPerson oPerson)

{

try

{

string sSQL = "INSERT INTO dbo_tblPerson(ID,Name,Surname,Email,Website,Address,Country,Comments,DateTime,Telephone,Mobile,Active) ";

sSQL += "VALUES (@.ID,@.Name,@.Surname,@.Email,@.Website,@.Address,@.Country,@.Comments,@.DateTime,@.Telephone,@.Mobile,@.Active)";

int i = 0;

int iHigh = getHighestID();

clsDatabase.ExecuteSQL(

sSQL,

new OleDbParameter("@.Name", oPerson.Name),

createNullableParameter("@.Surname", oPerson.Surname),

createNullableParameter("@.Email", oPerson.Email),

createNullableParameter("@.Website", oPerson.Website),

createNullableParameter("@.Address", oPerson.Address),

createNullableParameter("@.Country", oPerson.Country),

createNullableParameter("@.Comments", oPerson.Comments),

createNullableParameter("@.DateTime", oPerson.DateTime),

createNullableParameter("@.Telephone", oPerson.Telephone),

createNullableParameter("@.Mobile", oPerson.Mobile),

new OleDbParameter("@.Active", i)

);

return iHigh;

}

catch (Exception ex)

{

return 0;

//System.Reflection.MethodBase oExceptie = System.Reflection.MethodInfo.GetCurrentMethod();

//clsLogging.logException(1, "addInput: " + ex.Message, oExceptie);

}

}

private static int getHighestID()

{

int iID;

try

{

string sSQL = "SELECT MAX(ID) from dbo_tblPerson";

try

{

iID = Convert.ToInt32(clsDatabase.ExecuteScalar(sSQL, new OleDbParameter[0]));

}

catch (Exception ex)

{

iID = 0;

}

iID += 1;

}

catch (Exception ex)

{

throw ex;

}

return iID;

}

private static OleDbParameter createNullableParameter(string sName, object oProperty)

{

OleDbParameter oPar = new OleDbParameter() ;

if (oProperty == null)

{

oProperty = System.Convert.DBNull;

}

oPar.Value = oProperty;

oPar.ParameterName = sName;

return oPar;

}

Tobias

Apparently the databasefield name 'DateTime' caused problems, so I changed it to 'dt'.
Getting a whole new problem, being "Data type mismatch in criteria expression." Can it still be the datetime value thing?

Code Snippet

public static int addPerson(clsPerson oPerson)

{

try

{

string sSQL = "INSERT INTO dbo_tblPerson(Name,Surname,Email,Website,Address,Country,Comments,dt,Telephone,Mobile,Active) ";

sSQL += "VALUES (@.Name,@.Surname,@.Email,@.Website,@.Address,@.Country,@.Comments,@.dt,@.Telephone,@.Mobile,@.Active)";

int i = 0;

int iHigh = getHighestID();

clsDatabase.ExecuteSQL(

sSQL,

new OleDbParameter("@.Name", oPerson.Name),

createNullableParameter("@.Surname", oPerson.Surname),

createNullableParameter("@.Email", oPerson.Email),

createNullableParameter("@.Website", oPerson.Website),

createNullableParameter("@.Address", oPerson.Address),

createNullableParameter("@.Country", oPerson.Country),

createNullableParameter("@.Comments", oPerson.Comments),

createNullableParameter("@.dt", oPerson.DateTime),

createNullableParameter("@.Telephone", oPerson.Telephone),

createNullableParameter("@.Mobile", oPerson.Mobile),

new OleDbParameter("@.Active", i)

);

return iHigh;

}

catch (Exception ex)

{

return 0;

//System.Reflection.MethodBase oExceptie = System.Reflection.MethodInfo.GetCurrentMethod();

//clsLogging.logException(1, "addInput: " + ex.Message, oExceptie);

}

}

|||

This helped

oPerson.DateTime.ToString("MM-dd-yyyy hh:mms")

|||

I'm not sure what the definition is for "offline" system; but what about using SQL Server Express instead of Access?

|||

Then every client still has to run a server on their system .. don't they?

*edit: Anyway I 've succesfully implemented the access system already, just stuck now with how use it once distributed with Click Once. Don't know how to setup my Connectionstring for the local db.

|||

You mean an server operating system?

No, Express runs on XP(SP2) and Win2000 Pro (SP4)

|||But does the express edition act like a file or is it a running service? Because I can't ask for each client to install express on their system.|||

It runs as a service.

Sorry.

|||

That's what I thought, no worries.. glad I used the right way

You don't know a solution for my connectionstring problem? Or how to copy a file with Clickonce to c:\\DB\ ..?

|||

Not much help to you there...haven't worked with Access in a number of years.

|||

It doesn't really have to do with access.. I just need to put the access mdb file to a location I know on the users system using the ClickOnce Deployment, putting it between the application files works but then the location of the file would be something like

Code Snippet

C:\Documents and Settings\Tobias\Local Settings\Apps\2.0\2LQGDKTN.0KN\J3WQXOLX.BGB\netw...exe_75bfb84bc0f378ec_0000.0009_en-gb_fc79dfd558e684a4

So basically not usuable in a connectionstring for a database.