make table in back end, then link

[johnboy7676]

(Access 2003) I have code that works fine, but it makes the table in
the front end. I need to: check path to back end (in case it's
changed), either make the table in FE then move it to back end or just
make it in back end to start with, then make a link in the FE.

This is what I have now, on a form, which works fine to make a table
in the FE:

Dim strSQL As String
Dim strTableName As String

strTableName = "tblMasterFile" & Me.txtArchiveYear

strSQL = "SELECT tblMasterFile.* INTO " & strTableName & " FROM
tblMasterFile;"

DoCmd.RunSQL strSQL


Any pointers?
Thanks, John

 

[Marshall Barton]

(Access 2003) I have code that works fine, but it makes the table in
the front end. I need to: check path to back end (in case it's
changed), either make the table in FE then move it to back end or just
make it in back end to start with, then make a link in the FE.

This is what I have now, on a form, which works fine to make a table
in the FE:

Dim strSQL As String
Dim strTableName As String

strTableName = "tblMasterFile" & Me.txtArchiveYear

strSQL = "SELECT tblMasterFile.* INTO " & strTableName & " FROM
tblMasterFile;"

DoCmd.RunSQL strSQL

You can tell the query where to create the table by using an
IN phrase:

strSQL = "SELECT tblMasterFile.* INTO " & strTableName & "
IN ""path to back end mdb file"" FROM tblMasterFile;"


Note that it's usually better to use the Execute method
instead of the RunSQL method. With Execute your code will
not proceed until the query has done its job. With RunSQL
your code will continue in parallel the with the running
query.

 

[johnboy7676]

You can tell the query where to create the table by using an
IN phrase:

strSQL = "SELECT tblMasterFile.* INTO " & strTableName & "
IN ""path to back end mdb file"" FROM tblMasterFile;"


Note that it's usually better to use the Execute method
instead of the RunSQL method. With Execute your code will
not proceed until the query has done its job. With RunSQL
your code will continue in parallel the with the running
query.

Ok, I got the Path to Backend using this code I found.

strPathToBack =
Mid(CurrentDb().TableDefs("NameOfSomeLinkedTable").Connect,
11)

It works, but I'm curious to know what the 11 means, in "Connect,11"

So, doing as you suggest worked.

Reference doing Execute rather than RunSQL: After I posted the first
time, I realized I needed a parameter in the SQL, using
db.Execute strSQL, dbFailOnError which produced Error 3061, which I
googled and found this:

Dim db As DAO.Database, strSql As String
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Set db = CurrentDb()

Set qdf = db.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

Which works, but I'm curious as to how it works. I understand that
db.Execute strSQL, dbFailOnError doesn't work because of the
parameter, but I don't understand how the above gets the parameter
value? Gets it somehow for the original strSQL, obviously, but how?

Also, when using qdf.execute, is there an option such as
dbFailOnError, to let me know it failed?

Thanks, John

 

[Marshall Barton]

Ok, I got the Path to Backend using this code I found.

strPathToBack =
Mid(CurrentDb().TableDefs("NameOfSomeLinkedTable").Connect,
11)

It works, but I'm curious to know what the 11 means, in "Connect,11"

The full Connect property to a backend access database is:
;DATABASE=the path
The 11 tells Mid to start at the 11th character.

So, doing as you suggest worked.

Reference doing Execute rather than RunSQL: After I posted the first
time, I realized I needed a parameter in the SQL, using
db.Execute strSQL, dbFailOnError which produced Error 3061, which I
googled and found this:

Dim db As DAO.Database, strSql As String
Dim qdf As DAO.QueryDef, prm As DAO.Parameter
Set db = CurrentDb()

Set qdf = db.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

Which works, but I'm curious as to how it works. I understand that
db.Execute strSQL, dbFailOnError doesn't work because of the
parameter, but I don't understand how the above gets the parameter
value? Gets it somehow for the original strSQL, obviously, but how?

Queries have a parameters collection that the For Each loops
through. If your parameters look like
Forms!someform.somecontrol
(which is the name of the parameter), then the Eval function
can evaluate it as an expression to get the value from the
form control.

Also, when using qdf.execute, is there an option such as
dbFailOnError, to let me know it failed?

Sure,
qdf.Execute dbFailOnError

If you can decipher it, much of this info is in VBA Help.

 

[johnboy7676]

The full Connect property to a backend access database is:
;DATABASE=the path
The 11 tells Mid to start at the 11th character.


Queries have a parameters collection that the For Each loops
through. If your parameters look like
Forms!someform.somecontrol
(which is the name of the parameter), then the Eval function
can evaluate it as an expression to get the value from the
form control.


Sure,
qdf.Execute dbFailOnError

If you can decipher it, much of this info is in VBA Help.

Thanks!
John