Path as variable in maketable query

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

Guest

Is it possible to use a variable as pathname in a maketable query? And if so
how do I do this?
 
Is this whart you mean?

CurrentDb.QueryDefs("MyQuery").Sql = "SELECT Agenda.AgendaId, * INTO _
Agenda IN 'C:\Program Files\ReCaLc\Backup\InternepostBackend.mdb' FROM _
Agenda"

CurrentDb.QueryDefs("MyQuery").Execute
 
I added this to my module:

CurrentDb.QueryDefs("MyQuery").Sql = "SELECT Agenda.AgendaId, * INTO _
Agenda IN 'C:\Program Files\ReCaLc\Backup\InternepostBackend.mdb' FROM _
Agenda"
CurrentDb.QueryDefs("MyQuery").Execute


When executing I get an error:

'error 3265: can't find the element in this collection'
 
This did the trick for me, but you showed me the right way:

Dim Db As Database, Rs As Recordset, Sql
Set Db =
DBEngine.Workspaces(0).OpenDatabase("C:\ProgramFiles\ReCaLc\Backup\InternePostBackend.mdb", False, False)
Set Rs = Db.OpenRecordset("Agenda")

'First emty the backup database
Sql = "Delete * from Agenda IN
'C:\ProgramFiles\ReCaLc\Backup\InternePostBackend.mdb'"
DoCmd.RunSQL Sql

'Then fill it with the new data
Sql = "INSERT INTO Agenda IN 'C:\Program
Files\ReCaLc\Backup\InternePostBackend.mdb' SELECT * FROM Agenda"
DoCmd.RunSQL Sql
Rs.Close
Db.Close

This way I can create backups although other people are connected to my
backend database.
 
I managed it to work but I get a error when running the following :

Sql = "Delete * from " & RecordsetNaam & " IN '" & BackendNaam & "'"
DoCmd.RunSQL Sql

where RecordsetNaam is a table name with a space 'gemaakte offertes' wich I
think causes the error. How do I fix this? I tried several things but this
doesn't seem to work.

I get a error message when the Runsql starts like 'Error 3078: jet database
engine can't find the table or query Gemaakte....' So it doesn't see the
complete name but just the part before the space.

I tried:
RecordSetNaam = "[gemaakte offertes]"
RecordSetNaam = "gemaakte_offertes"
 
I wonder why you have some of the code there. I see no reason to open the
database or to have a recordset
The following should be all you need.

DIM Sql as String

'First emty the backup database
Sql = "Delete * from Agenda IN
'C:\ProgramFiles\ReCaLc\Backup\InternePostBackend.mdb'"
DoCmd.RunSQL Sql

'Then fill it with the new data
Sql = "INSERT INTO Agenda IN 'C:\Program
Files\ReCaLc\Backup\InternePostBackend.mdb' SELECT * FROM Agenda"
DoCmd.RunSQL Sql

Although if you were going to use the execute method instead of the RunSQL
method then you would need a database object.
Dim Db As Database, Sql as String
Set Db =
DBEngine.Workspaces(0).OpenDatabase("C:\ProgramFiles\ReCaLc\Backup\InternePostBackend.mdb",
False, False)


'First emty the backup database
Sql = "Delete * from Agenda IN
'C:\ProgramFiles\ReCaLc\Backup\InternePostBackend.mdb'"
Db.Execute Sql, dbFailOnError

'Then fill it with the new data
Sql = "INSERT INTO Agenda IN 'C:\Program
Files\ReCaLc\Backup\InternePostBackend.mdb' SELECT * FROM Agenda"
Db.Execute Sql, dbFailOnError

Db.Close
 
Back
Top