How to specify the name of the database to export to in a make table query

  • Thread starter Thread starter Neil Robbins
  • Start date Start date
N

Neil Robbins

I need to know whether it is possible to specify the name of a database to
export to in a make table query as a parameter. It would be best for me if
this were possible in SQL within the SQL view of Access. I am dealing with
an Access DB in 2000 file format. I want to execute the query from a .Net
application and I need to be able to use a filename and path specified by
the user.
 
Neil Robbins said:
I need to know whether it is possible to specify the name of a database to
export to in a make table query as a parameter. It would be best for me if
this were possible in SQL within the SQL view of Access. I am dealing with
an Access DB in 2000 file format. I want to execute the query from a .Net
application and I need to be able to use a filename and path specified by
the user.
Click to expand...

Syntax of SELECT INTO


SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The IN clause allows for the naming of a target external database.

{path | ["path" "type"] | ["" [type; DATABASE = path]]}

IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
IN "C:\DBASE\DATA\SALES" "dBASE IV;"

"type: is only required if it isn't an MS Access database.


Sincerely,

Chris O.
 
Thanks for this Chris,

What I need to know though is how to provide the database name & path as a
parameter in the query rather than as something explicitly specified within
the query. I have tried IN [Enter filename and path]. But this is not
recognised as a parameter and it thinks that 'Enter filename and path' is
the filename required.

Any further help would be greatly appreciated.

Regards,

Neil R.

Chris2 said:
Neil Robbins said:
I need to know whether it is possible to specify the name of a database to
export to in a make table query as a parameter. It would be best for me if
this were possible in SQL within the SQL view of Access. I am dealing with
an Access DB in 2000 file format. I want to execute the query from a ..Net
application and I need to be able to use a filename and path specified by
the user.
Click to expand...

Syntax of SELECT INTO


SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The IN clause allows for the naming of a target external database.

{path | ["path" "type"] | ["" [type; DATABASE = path]]}

IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
IN "C:\DBASE\DATA\SALES" "dBASE IV;"

"type: is only required if it isn't an MS Access database.


Sincerely,

Chris O.
Click to expand...
 
Hi Neil,

From your description, however, I'm unsure of your process of the
application and what query you would like to execute. Do you want to make
..net application query different database? Would you please show me your
process of application as detail as possible?

Base on my understand, you want to
1. get an input of database name and path(you have many different database
file in different file path)
2. do some query in the database you specified
3. show the query result in your .net application

In this case, I suggest that it is not necessary to use database name and
path as a parameter. in SQL query, instead you'd better make the query in
..net like this:
// Use constructor arguments to configure command object
SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );

// The above line is functionally equivalent to the following
// three lines which set properties explicitly
sqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM PRODUCTS";

It will be the same case, if you want to use the query in database.

More detailed information can be find at
..NET Data Access Architecture Guide
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daag.asp

Hope this help and detailed information that you can provide will make
things clear and help us move closer to the causes and resolutions.



Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Hi Michael,

I am trying to query a database, produce a table as a result of that query
and export it to a .mdb database that I have created especially for that one
table. The .mdb database that I create I would like the user to be able to
specify the location and name of. The problem is that the make table query
that I have created using Access specifies a name for the database. I would
like to have the make table query use a parameter for the name of the
database so that I can then provide the name and location that the user has
chosen.

So what I need to know is whether I can specify the name and location of the
database in the IN clause when I run the make table query from the .Net
application.

e.g. " INTO [exported_table] IN [enter filename] "
as opposed to
" INTO [exported_table] IN 'export_db.mdb' "

In the same way as in a WHERE clause you might use " WHERE
tablename.fieldname = [User decision here] " as opposed to " WHERE
tablename.fieldname = 'Smith' " for example.

Thanks for the response, any futher help would be most appreciated.

Regards,

Neil R.
 
So, you need to run SQL inside a Query, but need to supply an external
filepath?

Create a VBA Function

Public Function DatabaseFilePath(DbPathName as String) As String
Dim NewfilePath as String
'.
'Do Whatever to supply File Path
'.
DatabaseFilePath = NewFilePath
End Function

If no input data is needed, then change the above to a Sub instead of a
function. If the input data is not a String, then change that, too.


In the SQL

SELECT INTO NewTable IN DatabaseFilePath(OtherTable.ColumnName)
SELECT INTO NewTable IN DatabaseFilePath(Form!Field)
SELECT INTO NewTable IN DatabaseFilePath(etc.)
SELECT INTO NewTable IN DatabaseFilePath()

The last variant is used if the Function is changed to a sub.

Hmmm, you may have to enclose the function/sub in [], I'm not sure (yes, I
didn't test it).

SELECT INTO NewTable IN [DatabaseFilePath()]


Sincerely,

Chris O.



Neil Robbins said:
Thanks for this Chris,

What I need to know though is how to provide the database name & path as a
parameter in the query rather than as something explicitly specified within
the query. I have tried IN [Enter filename and path]. But this is not
recognised as a parameter and it thinks that 'Enter filename and path' is
the filename required.

Any further help would be greatly appreciated.

Regards,

Neil R.

database
to
Click to expand...
me
if
this were possible in SQL within the SQL view of Access. I am dealing with
an Access DB in 2000 file format. I want to execute the query from a .Net
application and I need to be able to use a filename and path specified by
the user.
Click to expand...

Syntax of SELECT INTO


SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The IN clause allows for the naming of a target external database.

{path | ["path" "type"] | ["" [type; DATABASE = path]]}

IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
IN "C:\DBASE\DATA\SALES" "dBASE IV;"

"type: is only required if it isn't an MS Access database.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
Hi Chris,

Thanks for this, sorry for the delay in replying. I have to run this in
vb.Net.

Is there a way of supplying the database name from a .Net windows solution?

Neil R.

I'm running the SQL code from a .NET application
Chris2 said:
So, you need to run SQL inside a Query, but need to supply an external
filepath?

Create a VBA Function

Public Function DatabaseFilePath(DbPathName as String) As String
Dim NewfilePath as String
'.
'Do Whatever to supply File Path
'.
DatabaseFilePath = NewFilePath
End Function

If no input data is needed, then change the above to a Sub instead of a
function. If the input data is not a String, then change that, too.


In the SQL

SELECT INTO NewTable IN DatabaseFilePath(OtherTable.ColumnName)
SELECT INTO NewTable IN DatabaseFilePath(Form!Field)
SELECT INTO NewTable IN DatabaseFilePath(etc.)
SELECT INTO NewTable IN DatabaseFilePath()

The last variant is used if the Function is changed to a sub.

Hmmm, you may have to enclose the function/sub in [], I'm not sure (yes, I
didn't test it).

SELECT INTO NewTable IN [DatabaseFilePath()]


Sincerely,

Chris O.



Neil Robbins said:
Thanks for this Chris,

What I need to know though is how to provide the database name & path as a
parameter in the query rather than as something explicitly specified within
the query. I have tried IN [Enter filename and path]. But this is not
recognised as a parameter and it thinks that 'Enter filename and path' is
the filename required.

Any further help would be greatly appreciated.

Regards,

Neil R.

Chris2 said:
I need to know whether it is possible to specify the name of a
Click to expand...
database
to
export to in a make table query as a parameter. It would be best for
Click to expand...
me
if
this were possible in SQL within the SQL view of Access. I am
Click to expand...
dealing
with
an Access DB in 2000 file format. I want to execute the query from a .Net
application and I need to be able to use a filename and path
Click to expand...
specified
by
the user.



Syntax of SELECT INTO


SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The IN clause allows for the naming of a target external database.

{path | ["path" "type"] | ["" [type; DATABASE = path]]}

IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
IN "C:\DBASE\DATA\SALES" "dBASE IV;"

"type: is only required if it isn't an MS Access database.


Sincerely,

Chris O.
Click to expand...
Click to expand...
Click to expand...
 
Hi Neil,

Based on my knowledge, you could make your query in .net like this
// Use constructor arguments to configure command object
SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );

// The above line is functionally equivalent to the following
// three lines which set properties explicitly
sqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM PRODUCTS";

you can use added String to make the whole query like String1 + "Table
Name" + String2

However, vb.net issue is related to .net language newsgroup. The reason why
we recommend posting appropriately is you will get the most qualified pool
of respondents, and other partners who read the newsgroups regularly can
either share their knowledge or learn from your interaction with us.

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.



Based on the description, the issue is related to the Msmdsrv.exe SQL
Server 2000 Analysis Services. To resolve this problem, obtain the latest
service pack for Microsoft SQL Server 2000.
 
Hi Neil,

Based on my knowledge, you could make your query in .net like this
// Use constructor arguments to configure command object
SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );

// The above line is functionally equivalent to the following
// three lines which set properties explicitly
sqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM PRODUCTS";
you can use added String to make the whole query like String1 + "Table
Name" + String2
Detailed information about this could be find at
..NET Data Access Architecture Guide
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daag.asp

However, vb.net issue is related to .net language newsgroup. The reason why
we recommend posting appropriately is you will get the most qualified pool
of respondents, and other partners who read the newsgroups regularly can
either share their knowledge or learn from your interaction with us.

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Hi Neil,

There is one KB article on it with sample code. Please refer to:
"HOW TO: Use the ODBC .NET Managed Provider in Visual C# .NET and
Connection Strings"
http://support.microsoft.com/?id=310988

The code like:
{
OdbcConnection cn;
OdbcCommand cmd;
string MyString;

MyString="Select * from Titles";

cn= new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};
DBQ=D:\\Program Files\\Microsoft
Office\\Office10\\Samples\\Northwind.mdb;UID=;PWD=;");

cmd=new OdbcCommand(MyString,cn);
cn.Open();
MessageBox.Show("Connected");

cn.Close();
}

Hope that helps.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top