Help on QBE to SQL

[MrDavePet]

Many times I use the Query tool to create a query, then I copy the SQL
and execute it from VBA.
I need to create a make table query to make a table in my database
from a table in another database. ( I can do it if I have aI link to
the other table)

I want an SQL statement that will create a table in my database but I
don't want to have the link to the external source table visible in my
database. I'm trying to keep the other database and its location
secret.

Can someone show me how?

 

[KenSheridan via AccessMonster.com]

You can use an IN clause to reference a table in an external database, e.g.

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' build and execute SQL statement
strSQL = "SELECT * INTO NewTable " & _
"FROM SomeTable " & _
"IN 'F:\SomeFolder\SomeSubfolder\SomeDb.mdb'"

cmd.CommandText = strSQL
cmd.Execute

' refresh database window to show new table
Application.RefreshDatabaseWindow

Of course savvy users might well be able to open the module and read the code,
which would make the location of the external table anything but secret.

Ken Sheridan
Stafford, England

 

[MGFoster]

Many times I use the Query tool to create a query, then I copy the SQL
and execute it from VBA.
I need to create a make table query to make a table in my database
from a table in another database. ( I can do it if I have aI link to
the other table)

I want an SQL statement that will create a table in my database but I
don't want to have the link to the external source table visible in my
database. I'm trying to keep the other database and its location
secret.

Can someone show me how?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. Right click on the table you want to hide.
2. Select Properties.
3. Check the Attributes: Hidden check box.
4. Click the OK button.

Note: A knowledgeable user can make the table visible by selecting from
the menu bar Tools > Options: View tab - and unchecking the "Hidden
objects" attribute in the Show section (this is Access 2002).

The best way to keep a user out of a table is to use User Security,
which is not available in Access 2007! Read the Access SecFAQ at:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q165009

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkpNXoechKqOuFEgEQLYXQCg8ttP7ASurgnHjdKGHOOwW+lId9UAmwRL
5YYmE7U0V7jltFgCzUyvTePI
=5zIz
-----END PGP SIGNATURE-----

 

[MrDavePet]

Thank you both.
I'm going to try the code version.