Path as variable in maketable query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ReMeE - 16 Nov 2006 07:31 GMT

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

Reply to this Message

Alex Dybenko - 16 Nov 2006 08:27 GMT

Hi,
you can build a query SQL in a code and then run it:

currentdb.querydefs("MyQuery").SQL = "Select into ..." & strFileName & "..."
currentdb.querydefs("MyQuery").execute

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

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

Reply to this Message

ReMeE - 16 Nov 2006 08:48 GMT

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

> Hi,
> you can build a query SQL in a code and then run it:
[quoted text clipped - 5 lines]
> > so
> > how do I do this?

Reply to this Message

ReMeE - 16 Nov 2006 08:51 GMT

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'

> Hi,
> you can build a query SQL in a code and then run it:
[quoted text clipped - 5 lines]
> > so
> > how do I do this?

Reply to this Message

Alex Dybenko - 16 Nov 2006 09:24 GMT

Yes, but myquery you have to replace with name of your query
and replace C:\Program Files\ReCaLc\Backup\InternepostBackend.mdb with:
" & strFileName & "

Signature

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

>I added this to my module:
>
[quoted text clipped - 18 lines]
>> > so
>> > how do I do this?

Reply to this Message

ReMeE - 16 Nov 2006 11:33 GMT

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"

> Yes, but myquery you have to replace with name of your query
> and replace C:\Program Files\ReCaLc\Backup\InternepostBackend.mdb with:
[quoted text clipped - 22 lines]
> >> > so
> >> > how do I do this?

Reply to this Message

ReMeE - 16 Nov 2006 09:18 GMT

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.

> Hi,
> you can build a query SQL in a code and then run it:
[quoted text clipped - 5 lines]
> > so
> > how do I do this?

Reply to this Message

John Spencer - 16 Nov 2006 12:34 GMT

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

> This did the trick for me, but you showed me the right way:
>
[quoted text clipped - 30 lines]
>> > so
>> > how do I do this?

Reply to this Message