Is Same acces to Access database and to table in SQL

Is it same ,except  the provider?
0
Pero
2/28/2009 5:12:33 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

10 Replies
383 Views

Similar Articles

[PageSpeed] 3

> Is it same ,except  the provider?

SQL server and Access have different capabilities and the SQL dialect has some differences.
0
Brian
3/2/2009 2:45:00 AM
> {quote:title=Pero perov wrote:}{quote}
> Is it same ,except  the provider?

As Brian said, there are differences.  For me, the most obvious difference was the way SQL in Access requires dates to be like this #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just use '03/17/2009'.

It should be easy to test your SQL in Access and the other database you are using, to see what syntax works and what syntax does not work.  If you are migrating from Access to SQL-Server, the only capability I know of that cannot be migrated with just a syntax change is the "IN <externaldatabase>" capabilities of Access.  However, if you are migrating TO Access, then I would expect to find more difficulties.
0
Mike
3/17/2009 6:38:53 AM
On Tue, 17 Mar 2009 06:38:53 -0000, Mike Ver Hagen  wrote:

> As Brian said, there are differences.  For me, the most obvious  
> difference was the way SQL in Access requires dates to be like this  
> #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just  
> use '03/17/2009'.

Even though Access and SQLserver require different delimiters round  
"dates", both will accept the date in YYYY-MM-DD format. This can save a  
lot of trouble - especially when the machines running the application and  
database are set up to different locales.

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
3/17/2009 8:52:05 AM
Good point.  I'll switch to the more generalized format.  Thanks.

> {quote:title=Paul Scott wrote:}{quote}
> On Tue, 17 Mar 2009 06:38:53 -0000, Mike Ver Hagen  wrote:
> 
> > As Brian said, there are differences.  For me, the most obvious  
> > difference was the way SQL in Access requires dates to be like this  
> > #03/17/2009# but in standard SQL, as we use with MS SQL-Server ,we just  
> > use '03/17/2009'.
> 
> Even though Access and SQLserver require different delimiters round  
> "dates", both will accept the date in YYYY-MM-DD format. This can save a  
> lot of trouble - especially when the machines running the application and  
> database are set up to different locales.
> 
> -- 
> Paul Scott
> Information Management Systems
> Macclesfield, UK
0
Mike
3/17/2009 9:41:07 PM
> I'll switch to the more generalized format.

Best is - always to use parameters.

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine
0
Dmitry
3/18/2009 4:43:29 AM
Dmitry,

On Wed, 18 Mar 2009 04:43:29 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
wrote:

>> I'll switch to the more generalized format.
>
> Best is - always to use parameters.

Although I would just point out that parameters are not a panacea - these  
groups have shown several problems with parameters.

Whether these actual problems were in the implementation of the parameter  
replacement mechanism or were incorrect understanding or usage by the  
programmer, the fact remains that parameter replacement is yet another  
"black box" which is very difficult to follow through if/when things go  
wrong.

Personally, I prefer the KISS approach.

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
3/18/2009 9:23:46 AM
> Although I would just point out that parameters are not a panacea -
> these groups have shown several problems with parameters.

Well ... If:
- N = number of problems, when app does not use parameters
- M = number of problems, when app does use parameters

Then M > 0, but N >> M 

<g>

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine
0
Dmitry
3/18/2009 12:33:57 PM
Dmitry,

On Wed, 18 Mar 2009 12:33:57 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
wrote:

>> Although I would just point out that parameters are not a panacea -
>> these groups have shown several problems with parameters.
>
> Well ... If:
> - N = number of problems, when app does not use parameters
> - M = number of problems, when app does use parameters
>
> Then M > 0, but N >> M
>
> <g>

True, but that "M > 0" term can still lead to a lot of wasted time - even  
if (especially if) the actual problem is not in the parameter replacement  
(this time <g>). Just being aware that it might possibly be a "parameter  
problem" can blind a programmer to the real faults in his SQL.

I find that building up the complete SQL command into a string variable  
makes it trivial to view the command in the debugger - and then to copy  
and paste it into Management Studio or Access and see /exactly/ what the  
database thinks of it. (Apart from the fiddly dialect changes made by ADO,  
of course!)

Even the supposed speed advantages from reusing "Prepared" commands turned  
out to be minimal and sometimes counter-productive..
http://msdn.microsoft.com/en-us/library/aa260835.aspx (see: "The  
(so-called) Prepared property")

Of course, YMMV :)

-- 
Paul Scott
Information Management Systems
Macclesfield, UK
0
Paul
3/18/2009 1:05:22 PM
Will not come into discussion of the parameters, just will say, 
that most of the beginners in a SQL application development
have **a lot** of problems at first with the incorrectly formated
constants in a SQL. The switch to a parameter usage in most 
cases solves the problems ...

> Even the supposed speed advantages from reusing "Prepared" commands
> turned out to be minimal and sometimes counter-productive..

I think, the main benefit of a parameter usage is an ability 
to use a client tool data representation and do not enter into
the details of a DBMS representation.

And there are few more hidden and/or DBMS-dependent 
benefits.

-- 
With best regards,
Dmitry Arefiev
AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL,
  Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX,
  ODBC, InMem - universal high-speed native data access engine
0
Dmitry
3/18/2009 5:11:17 PM
Paul:

I have never been able to use parameters for my most important and complicated queries because they vary A LOT depending on user input.  For instance, if a user has the option of selecting records from among these methods:

All agents
Agents selected in a list in the GUI
Agents with activity in some date range
Agents with activity involving specific other entities such as clients or products
plus sensible combinations of the above

the problem is that not all queries are going to specify the activity-related parameters (the last 2 in that list)

I have a lot of selection-criteria screens that offer selections such as those described above, plus several additional SETS of criteria like that, just for one report.

You don't have to go too far down that road to see that you have thousands of possible combinations (for just one report), each of which would have to be represented as a parameterized query.  I suppose you could build those on-the-fly, and maybe even save them in some databases if you could later detect that the desired combination already exists from an earlier run so you can use it again.

Absent a compelling business case for developing such a mechanism, I share your appreciation for the handiness of straight-up SQL generated by my app.  If one's fall-back interest in parameters is avoidance of SQL-injection, why not just apply screening for that?

mIKE

> {quote:title=Paul Scott wrote:}{quote}
> Dmitry,
> 
> On Wed, 18 Mar 2009 12:33:57 -0000, Dmitry Arefiev <darefiev@da-soft.com>  
> wrote:
> 
> >> Although I would just point out that parameters are not a panacea -
> >> these groups have shown several problems with parameters.
> >
> > Well ... If:
> > - N = number of problems, when app does not use parameters
> > - M = number of problems, when app does use parameters
> >
> > Then M > 0, but N >> M
> >
> > <g>
> 
> True, but that "M > 0" term can still lead to a lot of wasted time - even  
> if (especially if) the actual problem is not in the parameter replacement  
> (this time <g>). Just being aware that it might possibly be a "parameter  
> problem" can blind a programmer to the real faults in his SQL.
> 
> I find that building up the complete SQL command into a string variable  
> makes it trivial to view the command in the debugger - and then to copy  
> and paste it into Management Studio or Access and see /exactly/ what the  
> database thinks of it. (Apart from the fiddly dialect changes made by ADO,  
> of course!)
> 
> Even the supposed speed advantages from reusing "Prepared" commands turned  
> out to be minimal and sometimes counter-productive..
> http://msdn.microsoft.com/en-us/library/aa260835.aspx (see: "The  
> (so-called) Prepared property")
> 
> Of course, YMMV :)
> 
> -- 
> Paul Scott
> Information Management Systems
> Macclesfield, UK
0
Mike
3/27/2009 7:50:22 AM
Reply:

Similar Artilces:

Transfer tables and other contents from access database to sql database
Hi All, can anyone please help me to transfer tables and other contents from access database to sql database? I have access database file, can I also directly convert it into a sql database file? You can upsize your Access database to an SQL Server database (In Access, Tools>>Database Utilities>>Upsize Wizard.... follow the screen. Or you can use SQL Server's Import and Export wizard to import your access database to SQL Server.   You cannot convert your Access database file to SQL Server data file directly.Limno  Read this http://support.microsoft.com/kb/23...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

Link FoxPro Database table to SQL Server Database table
I have LAN Server in witch Visual Foxpro Database reside.I have another remote server in witch SQL Server 2005 installed.I want to link Foxpro table to SQL Server Database Table.Is it possible? Can please someone help? Thanks MS...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps. http://msdn2.microsoft.com/en-us/library/ms174335.aspx http://msdn2.microsoft.com/en-us/library/ms188029.aspx  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

Migrating Data from one Access Database table to a different Access Datbase / Table
A little background Company has a database that must have new records added to it weekly.  The new data is provided via an access database.  The old data is in a another access database.  What I want to do is have them upload the new access database to the ASP (not .net) server and then copy the new records over to the old data to bring it up to date.  At this point they can upload the new database. But I am having a hard time getting that info into the old database.   This is what I have so far,  but I get the following error Microsoft JET Database Engine...

New errors access MS SQL Server Database on (CREATE DATABASE and Batch SQL Statement)
------=_NextPart_000_002B_01C2F2CB.0C201FF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running ActiveState perl 5.8.0 on a WIN2K box with MS SQL Server = 2000. Recently I updated DBD-ODBC to v1.05 and DBI to v1.35 Now I get the following errors executing some sql commands CREATE SQLSTMT:[CREATE DATABASE POMS_SER ON (NAME =3D 'POMS_SER_DAT', = FILENAME =3D 'd:\Database\POMS\POMS_SER_Data.MDF', SIZE =3D 10MB) LOG ON = ( NAME =3D 'POMS_SER_LOG', FILENAME =3D = 'd:\Database\POMS\POMS_S...

Importing Access database/tables to SQL 2005 Express???
Is it possible to import an MS-Access database (or table) into a new SQL Server 2005 Express database? If so, how is it done? I haven't tried this in a while, but I used to be able to "link" SQL Server tables from my Access DB.  Not sure if SQL Server 2005 can be interfaced with MS Access, but if it could, then you should be able to simply copy/paste your data (or use the SQL statement)....

ADO Query to Append Tables from two Access Databases
I want to append a table form one database to a table from another database. Both tables have exactly same structure and the databases are developed using MS Access. How to write ADO query to do this? My query would be something like this: AppendQuery.SQL.Clear; AppendQuery.SQL.Add('insert into tbl1'); AppendQuery.SQL.Add('Select * '); AppendQuery.SQL.Add('from tbl2'); AppendQuery.ExecSQL; How to distinguish that tbl1 and tbl2 are from two different ADO Datasets in this query? To which datasource AppendQuery will be attached? Thanks. Shobha ...

how to export tables from SQL database to Access from a web application
Hi all, I am creating a web application that allow the users to choose which tables to export. Can anyone give me advice on how to go about doing this? I am able to show the data that the user choose on the web application itself. But I am stuck on exporting the tables to Access database.   Thks I'm not clear on what you're trying to accomplish. Is the objective to copy a table from an SQL database on the server to an Access database on the client?"Here's where the fun begins." -- Han Solo Yup, thats exactly what I want. Besides that, the user will ...

Insert into Access from two different sql database tables
Hi there, I am looking for a strategy on how to insert records into an MS Access table based on records from two different tables in different sql servers. Currently the solution uses a query stored in MS Access and implements linked tables to get data from the two sql tables. It works in Access if I manually enter the passwords to the SQL databases. When I tried to automate this from an asp.net page the sql connections failed. I edited the linked tables in Access so they store the password and that worked. Now if I manually run the query in Access it doesn't prompt for ...

xls convert sheet into Access database table and also convert access table into xls sheet in c#
hello sir how  xls convert sheet into Access database table and also convert access table into xls sheet in c# Hi, If you're working in Microsoft Excel and Microsoft Access, you can use several Access features to maintain your Excel data with the AccessLinks add-in program. For more details, see:http://office.microsoft.com/en-us/help/HA010346371033.aspx (For Excel2002) http://office.microsoft.com/en-us/excel/HP052008521033.aspx?pid=CH010003461033  (For Excel2003 and above.) Thanks.Michael Jin.Microsoft Online Community SupportPlease remember to mark the replies as answe...

Compatability problem Windows7 and ADO database acces from Delphi XE
All, I don't know where i need to post this, so i thought i'd try here, if someone can tell me where i need to put this posting. I have an application written in Delphi XE which uses ADO database connectivity. It retrieves data from an IBM i (AS400) using SQL either directly or via user defined table functions on the server side. (Queries are run via TADOConnection, TADODataset and TADOQuery) The application runs 100% perfectly on Windows XP, but when i try to run the program on Windows7 i have the following issues: 32 Bit. All the UDTFs work correctly except one. This is not...

Access Database and SQL Server database
Hi, I have an access database server and a sql server DB. The  Structure of both DB is same . The data is updated or inserted in access DB. I want to update SQL Server DB on a any event say button click. All updated data or new data in access DB should be reflected on SQL Server DB . Can anyone guide on the approach to be attempted. Thanks   Mark this as an answer if you find my it helpful.Thanks,Anubhuti ONe approach would be to use the System.Data.OleDb library and store connection strings for each database in your web.config file.  Each time you need ...

Accessing multiple tables of SQL database using JDBC driver
Hi, I have a requirement to update two tables with the JDBC driverl. I need to update one table first, from which an automatically generated field value(the primary key of this table, the foreign key in another table), needs to be fetched to be updated in another table. The update must be completed without opening the publisher channel. What I tried for the same was, I used SQL statements from my Output Transformation policy for inserting a record. Then I tried retrieving the same value. However, when I try to retrieve the value, I get a null. Here's the policy Iam usi...

Web resources about - Is Same acces to Access database and to table in SQL - embarcadero.delphi.ado

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Social Side of Code, Database CI and REST API Testing in Methods & Tools Winter 2015 issue
... for software developers, testers and project managers – has published its Winter 2015 issue that discusses the social side of code, database ...

Enterprises want to run databases in containers
More than three out of four IT decision makers are interested in running stateful applications like databases within containers, according to ...

Huge database of 191 million U.S. voters found on the open Internet
... such bulk information online, a recent discovery seems to indicate. According to VentureBeat , a security researcher found the exposed database ...

Top US School District Switches to Rimini Street for Oracle Database and Oracle E-Business Suite Support ...
Douglas County School District using savings from Rimini Street independent support to develop district-wide data visualization capabilities ...

IDG Contributor Network: Shrinking an already ginormous SharePoint database transaction log
Last month, I wrote about how to avoid having ginormous transaction logs within SharePoint databases. But what if it’s too late, and your files ...

18 million targeted voter records exposed by database error
A second database containing voter records has been exposed due to a configuration error. While the first held records that some dismissed inconsequential ...

Former Ohio cop uses cop database to stalk victim
... stalked her like an average person would, but stalked her in the way that only a police officer could: he stalked her utilizing a police database ...

U.S. Attorney Suggests Password Cardinals Used To Access Astros Scouting Database Was “Eckstein123"
... scouting director Christopher Correa plead guilty to five counts of unauthorized access to a computer for hacking the Houston Astros database. ...

Resources last updated: 2/3/2016 9:30:49 PM