Can I create a temp table variable in access 2003-07 as we can create in SQL server?

and is there any system defined variable in ms access i.e @@variablename etc... ?


regards.

__________________
Stay Beautiful,
Abdul Salam

Normally a stored procedure or advanced query in SQL Server would be done with VBA code and actual local tables in Access. There are several ways to create a table and then use it, and then delete it using code. Generally I just create the table and then use it as needed and then delete everything from it when done.

What sort of transactions do you want to create in Access?



mmcdonal

Look it up at: http://wrox.books24x7.com

Thank you for answer...

Actually i want to solve problem which i have posted earlier " http://p2p.wrox.com/topic.asp?TOPIC_ID=67693 "

the reason of doing all only through query is that i am writing each n every thing in SQL Standard so it can be shifted at any other engine like Oracle, SQL Server, MS Access...


regards

As far as I know, Access is still using SQL ANSI 89, while SQL Server 2000 uses ANSI 92, so Access sql statements are not always transportable anyway.


mmcdonal

Look it up at: http://wrox.books24x7.com

hummmm.....

then what do you suggest...

Oracle and SQL Server use different Provider and SQL strings anyway. For example, if you want to connect to SQL Server, you would do this:

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;UID=username;PWD=password"

Then: "SELECT * FROM MyTable"

To connect to Oracle:

Set cn = New ADODB.Connection
cn.Open "Provider=msdaora;Data Source=Instance.Domain;User ID=UserID;Password=password;"

Then: "SELECT * FROM schema.MyTable"

Etc. So syntax is always going to be an issue when you change the back end.

I would do what you need to do for the current front end/back end, and then rewrite as needed if and when a new front end/back end combination is needed.

Did that help?






mmcdonal

Look it up at: http://wrox.books24x7.com