Trouble with SELECT ... INTO SQL statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all.

I'm trying to use some VBA to browse through the Tables of my Access DB and
to create Back-ups in a separate backup Access file for the ones i want.

To achieve the latter, i'm trying to use an SQL SELECT ... INTO statement
identical to the one that's being created if i design a Make-Table Query.

If i design and run the Query, it's being executed ok and the Table i
include in the Query design is indeed created in the backup Access file.
However, if i try to use the Query's SQL statement to execute the same
action via VBA, it fails.

Here's the related part of my VBA code:

strSQL = "SELECT [" & BackupTbls(iCnt) & "].* " _
& "INTO [" & BackupTbls(iCnt) & "] IN '" & strBackupDB & "' " _
& "FROM [" & BackupTbls(iCnt) & "]"
CurrentDb.Connection.Execute strSQL

Notes:
- BackupTbls() is a String type array containing the Tables i want to backup
- strBackupDB is the full name of the backup Access file (Path + Name)
- The strSQL string is IDENTICAL to the one used by the Access Query (except
for the ';' in the end in the Query string)

The error message im getting is:

Run-time error '3251'
Operation is not supported for this type of object.

This appears only when trying to execute via VBA, the normal Access Query
(with the identical SQL string) executes ok.

I'd be grateful to have any suggestions on this.

Kind regards,
Alex
 
Alex:

Try the following instead:

CurrentDb.Execute strSQL

I believe the Connection object of the CurrentDb object is valid for
ODBCDirect workspaces only.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.



Hi all.

I'm trying to use some VBA to browse through the Tables of my Access DB and
to create Back-ups in a separate backup Access file for the ones i want.

To achieve the latter, i'm trying to use an SQL SELECT ... INTO statement
identical to the one that's being created if i design a Make-Table Query.

If i design and run the Query, it's being executed ok and the Table i
include in the Query design is indeed created in the backup Access file.
However, if i try to use the Query's SQL statement to execute the same
action via VBA, it fails.

Here's the related part of my VBA code:

strSQL = "SELECT [" & BackupTbls(iCnt) & "].* " _
& "INTO [" & BackupTbls(iCnt) & "] IN '" & strBackupDB & "' " _
& "FROM [" & BackupTbls(iCnt) & "]"
CurrentDb.Connection.Execute strSQL

Notes:
- BackupTbls() is a String type array containing the Tables i want to backup
- strBackupDB is the full name of the backup Access file (Path + Name)
- The strSQL string is IDENTICAL to the one used by the Access Query (except
for the ';' in the end in the Query string)

The error message im getting is:

Run-time error '3251'
Operation is not supported for this type of object.

This appears only when trying to execute via VBA, the normal Access Query
(with the identical SQL string) executes ok.

I'd be grateful to have any suggestions on this.

Kind regards,
Alex
 
Thanks David, i had tried this already, but unfortunately i got an 'Invalid
bracketing' error message.

I finally resorted to the following statement, and thankfully it works:
DoCmd.CopyObject strBackupDB, , acTable, BackupTbls(iCnt)

Thanks anyway!
Alex
 
Look at the TransferDatabase function in the Help file and see if it is more
appropriate for what you want to do.
 
Back
Top