Extract a table to mdb file with vba

[jean]

Hi

I have an Access file containing 20 tables

One of the table is named "TEmplTS"

I am trying to find the right code (that I will paste to a button)
to :

Create an Access file named "Time Sheet Table.mdb" containing only the
table "TEmplTS" and export it into the directory "c:\Time Sheet"

I already have the code to create the directory if it don't exist

But I am stuck with the first part

thanks for helping

 

[John W. Vinson]

Hi

I have an Access file containing 20 tables

One of the table is named "TEmplTS"

I am trying to find the right code (that I will paste to a button)
to :

Create an Access file named "Time Sheet Table.mdb" containing only the
table "TEmplTS" and export it into the directory "c:\Time Sheet"

I already have the code to create the directory if it don't exist

But I am stuck with the first part

thanks for helping

Take a look at the VBA Help for "CreateDatabase" to create the .mdb file, and
"TransferDatabase" to export the table into the newly created database.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Tony Toews]

Create an Access file named "Time Sheet Table.mdb" containing only the
table "TEmplTS" and export it into the directory "c:\Time Sheet"

I already have the code to create the directory if it don't exist

See the TempTables.MDB page which shows how to create an MDB and a
table in that MDB. http://www.granite.ab.ca/access/temptables.htm

Then you could use an IN clause in a query to copy the records from
your current MDB to the new MDB. From A97 help:

IN Clause - Identifies tables in any external database to which the
Microsoft Jet database engine can connect, such as a dBASE or Paradox
database or an external Microsoft Jet database.

To identify a destination table:
INSERT INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

For example (word wrap will make this ugly)

strSQL = "INSERT INTO ServiceRecordInvoices " & _
"( sriID, sriServiceRecordID, sriInvoiceDate,
sriInvoiceNumber, sriDescription, sriInvoiceAmount ) " & _
" IN '" & strDatabasePathandName & "' " & _
"SELECT srpID, srpServiceRecordID, srpInvoiceDate,
srpInvoiceNumber, srpParts, srpPartsAmount " & _
"FROM ServiceRecordParts IN '" & strDatabasePathandName & "';"
CurrentDb.Execute strSQL, dbFailOnError

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[James A. Fortune]

Create an Access file named "Time Sheet Table.mdb" containing only the
table "TEmplTS" and export it into the directory "c:\Time Sheet"

I already have the code to create the directory if it don't exist

See the TempTables.MDB page which shows how to create an MDB and a
table in that MDB.http://www.granite.ab.ca/access/temptables.htm 

Then you could use an IN clause in a query to copy the records from
your current MDB to the new MDB.  From A97 help:

IN Clause - Identifies tables in any external database to which the
Microsoft Jet database engine can connect, such as a dBASE or Paradox
database or an external Microsoft Jet database.

To identify a destination table:
INSERT INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

For example (word wrap will make this ugly)

    strSQL = "INSERT INTO ServiceRecordInvoices " & _
        "( sriID, sriServiceRecordID, sriInvoiceDate,
sriInvoiceNumber, sriDescription, sriInvoiceAmount ) " & _
        " IN '" & strDatabasePathandName & "' " & _
        "SELECT srpID, srpServiceRecordID, srpInvoiceDate,
srpInvoiceNumber, srpParts, srpPartsAmount " & _
        "FROM ServiceRecordParts IN '" & strDatabasePathandName &"';"
    CurrentDb.Execute strSQL, dbFailOnError

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

Using the 'IN' keyword works, but the alternate syntax suggested in
the following link can handle any situation that the 'IN' keyword can,
plus it can handle many situations that the 'IN' keyword cannot. See:

http://groups.google.com/group/microsoft.public.access/msg/bad52a3720d21c96

James A. Fortune
(e-mail address removed)

 

[Tony Toews]

Using the 'IN' keyword works, but the alternate syntax suggested in
the following link can handle any situation that the 'IN' keyword can,
plus it can handle many situations that the 'IN' keyword cannot. See:

Presumably you mean the following:
SELECT * FROM [M:\Databases\DB2.mdb].tblImageBuild INNER JOIN
[M:\Databases\DB2.mdb]

I don't think I've seen exactly that syntax before.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[Douglas J Steele]

No, that's invalid syntax: you're missing the table name for the second
table.

As James says, it's a much more flexible approach. Obviously it must not be
necessary, but I always include ;Database= in there:

SELECT * FROM [;Database=M:\Databases\DB2.mdb].tblImageBuild INNER JOIN
[;Database=M:\Databases\DB2.mdb].tblOther ON ...

"Tony Toews" wrote in message

Using the 'IN' keyword works, but the alternate syntax suggested in
the following link can handle any situation that the 'IN' keyword can,
plus it can handle many situations that the 'IN' keyword cannot. See:

Presumably you mean the following:
SELECT * FROM [M:\Databases\DB2.mdb].tblImageBuild INNER JOIN
[M:\Databases\DB2.mdb]

I don't think I've seen exactly that syntax before.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[a a r o n . k e m p f]

wow, I don't have any trouble joining stuff from two different linked
servers

maybe you kids should lose the training wheels and wake up to the 20th
century!

No, that's invalid syntax: you're missing the table name for the second
table.

As James says, it's a much more flexible approach. Obviously it must not be
necessary, but I always include ;Database= in there:

SELECT * FROM [;Database=M:\Databases\DB2.mdb].tblImageBuild INNER JOIN
[;Database=M:\Databases\DB2.mdb].tblOther ON ...

"Tony Toews"  wrote in message

Using the 'IN' keyword works, but the alternate syntax suggested in
the following link can handle any situation that the 'IN' keyword can,
plus it can handle many situations that the 'IN' keyword cannot.  See:

Presumably you mean the following:
SELECT * FROM [M:\Databases\DB2.mdb].tblImageBuild INNER JOIN
[M:\Databases\DB2.mdb]

I don't think I've seen exactly that syntax before.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/

 

[James A. Fortune]

wow, I don't have any trouble joining stuff from two different linked
servers

How about joining two spreadsheets? Given the source of the syntax,
it's got to be the same as that available in SQL Server. If SQL
Server is so great, why would the syntax you disparage out of
ignorance of its origin arise from it?

maybe you kids should lose the training wheels and wake up to the 20th
century!

I agree that Access and SQL Server (include any product that uses SQL
or LINQ) use 20th century technology, but we're now in the the 21st
century! Are you advocating that no one use SQL Server or Access or
LINQ anymore because they're based on old concepts? The technology
may be old, but there's still a place for relational databases, IMO.
Your argument actually makes the case for moving away from archaic SQL
Server to something more modern. If your purpose is to get people to
move from using Access backends to SQL Server backends, then you
should stick to relevant reasons for doing so lest your arguments for
moving away from Access become the same arguments for moving away from
SQL Server later. Many people throw logic out the door if it helps
them achieve their purpose.

James A. Fortune
(e-mail address removed)

A man will not lie unless he believes there is some advantage to be
gained by it. -- Alexander the Great