path to backend

S

SAC

I'm using a frontend/backend set up with linked tables. How can I make a
string = to the path to the backend?

strPathToBack = db.connect does work where db = currentdb.

Thanks
 
D

Douglas J Steele

I assume you meant that your example doesn't work... Try:

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

SAC

Thanks!

Douglas J Steele said:
I assume you meant that your example doesn't work... Try:

strPathToBack = Mid(CurrentDb().TableDefs("NameOfSomeLinkedTable").Connect,
11)
Click to expand...
 
S

SAC

OK, I put the information you gave me into a string and now I get an error
that says "Query input must contain one table or query....here's the
code...when I had code the path it works...so I don't knoiw what I'm doing
wrong.

Function MakeOpenOrdersTable()
Dim db As Database
Dim strsql As String
Dim strtmp As String
Set db = CurrentDb

strtmp = Mid(CurrentDb.TableDefs("TWDXRF").Connect, 11)

MsgBox (strtmp) '*****************This looks right******************


strsql = "SELECT TWDHPH.PHENDT, IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0) AS ActualShipDate, IIf(IsNull([ActualShipDate])
Or [ActualShipDate]>=Now()-60,-1,0) " _
& "AS OpenOrd, tblCustomerMaster.CCUST, tblCustomerMaster.CNME,
TWDXRF.XCPO, TWDHPO.PPROD, TWDHPO.PVEND, " _
& "TWDXRF.XTPO, TWDHPO.PLINE, TWDHPO.PQORD, TWDLIN.LSHQTY, TWDECH.HEDTE,
TWDHPO.ConfirmedShpDate, " _
& "TWDHPO.ScheduledDate, TWDXRF.XCORD, TWDXRF.XCUST, TWDXRF.XTVND,
tblVendorMaster.VNDNAM, TWDIIM.IDESC, " _
& "tblBuyer.DESC AS BUYER, TWDLIN.LBOL, TWDLIN.LESTDP,
tblCustomerMaster.CREF02 AS PM INTO tblTmpQuery3 " _
& "IN " & strtmp & " " & vbCrLf _
& "FROM (((((((TWDXRF LEFT JOIN TWDHPO ON TWDXRF.XTPO = TWDHPO.PORD)
LEFT JOIN tblCustomerMaster ON " _
& "TWDXRF.XCUST = tblCustomerMaster.CCUST) LEFT JOIN tblVendorMaster ON
TWDXRF.XTVND = tblVendorMaster.VENDOR) " _
& "LEFT JOIN TWDIIM ON TWDHPO.PPROD = TWDIIM.IPROD) LEFT JOIN tblBuyer
ON TWDIIM.IBUYC = tblBuyer.IPURC) " _
& "LEFT JOIN TWDLIN ON (TWDHPO.PORD = TWDLIN.LTWPO) AND (TWDHPO.PLINE =
TWDLIN.LTWPOL)) " _
& "LEFT JOIN TWDHPH ON TWDHPO.PORD = TWDHPH.PHORD) LEFT JOIN TWDECH ON
TWDXRF.XCORD = TWDECH.HORD " _
& "ORDER BY TWDHPH.PHENDT DESC , IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0), tblCustomerMaster.CNME, TWDXRF.XCPO,
TWDHPO.PPROD;"

'This was the line the string replaced:

'& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _
'***************This works with it hard-coded******************

MsgBox (strsql) '*****************This look right******************

DoCmd.RunSQL strsql

set db = nothing

End Function

The path looks right from the string when I put it in a msgbox and the sql
statement looks right from the msgbox.

Any ideas?

Thanks for your help.
 
R

Robin

This works for me and returns the full path and filename for the backend -
you just need to name a table in the backend that you're looking for:

Public Function test()
Dim db As Database
Set db = CurrentDb
test = db.TableDefs(tablename).Connect
End Function
 
R

Robin

I might be missing something, but why would you want to identify the path
from the 11th character onwards?
 
J

John Mishefske

SAC said:
I'm using a frontend/backend set up with linked tables. How can I make a
string = to the path to the backend?

strPathToBack = db.connect does work where db = currentdb.
Click to expand...


What if you upgrade the FE .mdb file? How does the client get back to his database?
One method it to store the path to the back end .mdb file in a text file that won't be
destroyed during the next install.

That is, it survives an upgrade. Another option is to write it to the registry...
 
S

SAC

I agree..I get this to work but when I put the string variable into the sql
statement it doesn't work. Please
see my previous post in this thread.

Thanks for your help.
 
S

SAC

How would upgrading the fe help?

What do you mean by "How does the client get back to his database?"

Thanks.
 
D

Douglas J Steele

Doesn't make sense to me either.

Try replacing the line

MsgBox (strsql)

with

Debug.Print strSQL

then going to the immediate window (Ctrl-G) to see the actual SQL (as
opposed to just looking at it in a message box). What happens when you copy
that SQL into a new query and try to run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SAC said:
OK, I put the information you gave me into a string and now I get an error
that says "Query input must contain one table or query....here's the
code...when I had code the path it works...so I don't knoiw what I'm doing
wrong.

Function MakeOpenOrdersTable()
Dim db As Database
Dim strsql As String
Dim strtmp As String
Set db = CurrentDb

strtmp = Mid(CurrentDb.TableDefs("TWDXRF").Connect, 11)

MsgBox (strtmp) '*****************This looks right******************


strsql = "SELECT TWDHPH.PHENDT, IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0) AS ActualShipDate, IIf(IsNull([ActualShipDate])
Or [ActualShipDate]>=Now()-60,-1,0) " _
& "AS OpenOrd, tblCustomerMaster.CCUST, tblCustomerMaster.CNME,
TWDXRF.XCPO, TWDHPO.PPROD, TWDHPO.PVEND, " _
& "TWDXRF.XTPO, TWDHPO.PLINE, TWDHPO.PQORD, TWDLIN.LSHQTY, TWDECH.HEDTE,
TWDHPO.ConfirmedShpDate, " _
& "TWDHPO.ScheduledDate, TWDXRF.XCORD, TWDXRF.XCUST, TWDXRF.XTVND,
tblVendorMaster.VNDNAM, TWDIIM.IDESC, " _
& "tblBuyer.DESC AS BUYER, TWDLIN.LBOL, TWDLIN.LESTDP,
tblCustomerMaster.CREF02 AS PM INTO tblTmpQuery3 " _
& "IN " & strtmp & " " & vbCrLf _
& "FROM (((((((TWDXRF LEFT JOIN TWDHPO ON TWDXRF.XTPO = TWDHPO.PORD)
LEFT JOIN tblCustomerMaster ON " _
& "TWDXRF.XCUST = tblCustomerMaster.CCUST) LEFT JOIN tblVendorMaster ON
TWDXRF.XTVND = tblVendorMaster.VENDOR) " _
& "LEFT JOIN TWDIIM ON TWDHPO.PPROD = TWDIIM.IPROD) LEFT JOIN tblBuyer
ON TWDIIM.IBUYC = tblBuyer.IPURC) " _
& "LEFT JOIN TWDLIN ON (TWDHPO.PORD = TWDLIN.LTWPO) AND (TWDHPO.PLINE =
TWDLIN.LTWPOL)) " _
& "LEFT JOIN TWDHPH ON TWDHPO.PORD = TWDHPH.PHORD) LEFT JOIN TWDECH ON
TWDXRF.XCORD = TWDECH.HORD " _
& "ORDER BY TWDHPH.PHENDT DESC , IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0), tblCustomerMaster.CNME, TWDXRF.XCPO,
TWDHPO.PPROD;"

'This was the line the string replaced:

'& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _
'***************This works with it hard-coded******************

MsgBox (strsql) '*****************This look right******************

DoCmd.RunSQL strsql

set db = nothing

End Function

The path looks right from the string when I put it in a msgbox and the sql
statement looks right from the msgbox.

Any ideas?

Thanks for your help.


Douglas J Steele said:
I assume you meant that your example doesn't work... Try:

strPathToBack = Mid(CurrentDb().TableDefs("NameOfSomeLinkedTable").Connect,
11)
Click to expand...
make
a
Click to expand...
Click to expand...
 
S

SAC

It looks fine in the intermediate window.

If I take out the

IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb'

line it works.

??







Douglas J Steele said:
Doesn't make sense to me either.

Try replacing the line

MsgBox (strsql)

with

Debug.Print strSQL

then going to the immediate window (Ctrl-G) to see the actual SQL (as
opposed to just looking at it in a message box). What happens when you copy
that SQL into a new query and try to run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SAC said:
OK, I put the information you gave me into a string and now I get an error
that says "Query input must contain one table or query....here's the
code...when I had code the path it works...so I don't knoiw what I'm doing
wrong.

Function MakeOpenOrdersTable()
Dim db As Database
Dim strsql As String
Dim strtmp As String
Set db = CurrentDb

strtmp = Mid(CurrentDb.TableDefs("TWDXRF").Connect, 11)

MsgBox (strtmp) '*****************This looks right******************


strsql = "SELECT TWDHPH.PHENDT, IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0) AS ActualShipDate, IIf(IsNull([ActualShipDate])
Or [ActualShipDate]>=Now()-60,-1,0) " _
& "AS OpenOrd, tblCustomerMaster.CCUST, tblCustomerMaster.CNME,
TWDXRF.XCPO, TWDHPO.PPROD, TWDHPO.PVEND, " _
& "TWDXRF.XTPO, TWDHPO.PLINE, TWDHPO.PQORD, TWDLIN.LSHQTY, TWDECH.HEDTE,
TWDHPO.ConfirmedShpDate, " _
& "TWDHPO.ScheduledDate, TWDXRF.XCORD, TWDXRF.XCUST, TWDXRF.XTVND,
tblVendorMaster.VNDNAM, TWDIIM.IDESC, " _
& "tblBuyer.DESC AS BUYER, TWDLIN.LBOL, TWDLIN.LESTDP,
tblCustomerMaster.CREF02 AS PM INTO tblTmpQuery3 " _
& "IN " & strtmp & " " & vbCrLf _
& "FROM (((((((TWDXRF LEFT JOIN TWDHPO ON TWDXRF.XTPO = TWDHPO.PORD)
LEFT JOIN tblCustomerMaster ON " _
& "TWDXRF.XCUST = tblCustomerMaster.CCUST) LEFT JOIN tblVendorMaster ON
TWDXRF.XTVND = tblVendorMaster.VENDOR) " _
& "LEFT JOIN TWDIIM ON TWDHPO.PPROD = TWDIIM.IPROD) LEFT JOIN tblBuyer
ON TWDIIM.IBUYC = tblBuyer.IPURC) " _
& "LEFT JOIN TWDLIN ON (TWDHPO.PORD = TWDLIN.LTWPO) AND
Click to expand...
(TWDHPO.PLINE
=
TWDLIN.LTWPOL)) " _
& "LEFT JOIN TWDHPH ON TWDHPO.PORD = TWDHPH.PHORD) LEFT JOIN TWDECH ON
TWDXRF.XCORD = TWDECH.HORD " _
& "ORDER BY TWDHPH.PHENDT DESC , IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0), tblCustomerMaster.CNME, TWDXRF.XCPO,
TWDHPO.PPROD;"

'This was the line the string replaced:

'& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _
'***************This works with it hard-coded******************

MsgBox (strsql) '*****************This look right******************

DoCmd.RunSQL strsql

set db = nothing

End Function

The path looks right from the string when I put it in a msgbox and the sql
statement looks right from the msgbox.

Any ideas?

Thanks for your help.


Douglas J Steele said:
I assume you meant that your example doesn't work... Try:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm using a frontend/backend set up with linked tables. How can I
Click to expand...
make
a
string = to the path to the backend?

strPathToBack = db.connect does work where db = currentdb.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
S

SAC

If I leave this in it works:

& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _

(When I install it at the customer the path is different so I need to make
the path dynamic)

When I put this in it bombs:

& "IN " & strtmp & " " & vbCrLf _

(Where strtmp is Mid(CurrentDb().TableDefs("TWDHECH").Connect, 11)

the contents of strtmp shows up correctly.

Can the "IN" line go before the FROM clause?

I've tried it both ways and still get an error.

The hard-coded path works and I don't see any diff in the hard-coded verses
the strtmp.

??

Thanks.






Douglas J Steele said:
Doesn't make sense to me either.

Try replacing the line

MsgBox (strsql)

with

Debug.Print strSQL

then going to the immediate window (Ctrl-G) to see the actual SQL (as
opposed to just looking at it in a message box). What happens when you copy
that SQL into a new query and try to run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SAC said:
OK, I put the information you gave me into a string and now I get an error
that says "Query input must contain one table or query....here's the
code...when I had code the path it works...so I don't knoiw what I'm doing
wrong.

Function MakeOpenOrdersTable()
Dim db As Database
Dim strsql As String
Dim strtmp As String
Set db = CurrentDb

strtmp = Mid(CurrentDb.TableDefs("TWDXRF").Connect, 11)

MsgBox (strtmp) '*****************This looks right******************


strsql = "SELECT TWDHPH.PHENDT, IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0) AS ActualShipDate, IIf(IsNull([ActualShipDate])
Or [ActualShipDate]>=Now()-60,-1,0) " _
& "AS OpenOrd, tblCustomerMaster.CCUST, tblCustomerMaster.CNME,
TWDXRF.XCPO, TWDHPO.PPROD, TWDHPO.PVEND, " _
& "TWDXRF.XTPO, TWDHPO.PLINE, TWDHPO.PQORD, TWDLIN.LSHQTY, TWDECH.HEDTE,
TWDHPO.ConfirmedShpDate, " _
& "TWDHPO.ScheduledDate, TWDXRF.XCORD, TWDXRF.XCUST, TWDXRF.XTVND,
tblVendorMaster.VNDNAM, TWDIIM.IDESC, " _
& "tblBuyer.DESC AS BUYER, TWDLIN.LBOL, TWDLIN.LESTDP,
tblCustomerMaster.CREF02 AS PM INTO tblTmpQuery3 " _
& "IN " & strtmp & " " & vbCrLf _
& "FROM (((((((TWDXRF LEFT JOIN TWDHPO ON TWDXRF.XTPO = TWDHPO.PORD)
LEFT JOIN tblCustomerMaster ON " _
& "TWDXRF.XCUST = tblCustomerMaster.CCUST) LEFT JOIN tblVendorMaster ON
TWDXRF.XTVND = tblVendorMaster.VENDOR) " _
& "LEFT JOIN TWDIIM ON TWDHPO.PPROD = TWDIIM.IPROD) LEFT JOIN tblBuyer
ON TWDIIM.IBUYC = tblBuyer.IPURC) " _
& "LEFT JOIN TWDLIN ON (TWDHPO.PORD = TWDLIN.LTWPO) AND
Click to expand...
(TWDHPO.PLINE
=
TWDLIN.LTWPOL)) " _
& "LEFT JOIN TWDHPH ON TWDHPO.PORD = TWDHPH.PHORD) LEFT JOIN TWDECH ON
TWDXRF.XCORD = TWDECH.HORD " _
& "ORDER BY TWDHPH.PHENDT DESC , IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0), tblCustomerMaster.CNME, TWDXRF.XCPO,
TWDHPO.PPROD;"

'This was the line the string replaced:

'& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _
'***************This works with it hard-coded******************

MsgBox (strsql) '*****************This look right******************

DoCmd.RunSQL strsql

set db = nothing

End Function

The path looks right from the string when I put it in a msgbox and the sql
statement looks right from the msgbox.

Any ideas?

Thanks for your help.


Douglas J Steele said:
I assume you meant that your example doesn't work... Try:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm using a frontend/backend set up with linked tables. How can I
Click to expand...
make
a
string = to the path to the backend?

strPathToBack = db.connect does work where db = currentdb.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
S

SAC

Got it!

I had to put ' around the string....."'" & strtmp & "'"

It was reassuring that it SHOULD work so I could working at it.

Thanks for your help!

Douglas J Steele said:
Doesn't make sense to me either.

Try replacing the line

MsgBox (strsql)

with

Debug.Print strSQL

then going to the immediate window (Ctrl-G) to see the actual SQL (as
opposed to just looking at it in a message box). What happens when you copy
that SQL into a new query and try to run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SAC said:
OK, I put the information you gave me into a string and now I get an error
that says "Query input must contain one table or query....here's the
code...when I had code the path it works...so I don't knoiw what I'm doing
wrong.

Function MakeOpenOrdersTable()
Dim db As Database
Dim strsql As String
Dim strtmp As String
Set db = CurrentDb

strtmp = Mid(CurrentDb.TableDefs("TWDXRF").Connect, 11)

MsgBox (strtmp) '*****************This looks right******************


strsql = "SELECT TWDHPH.PHENDT, IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0) AS ActualShipDate, IIf(IsNull([ActualShipDate])
Or [ActualShipDate]>=Now()-60,-1,0) " _
& "AS OpenOrd, tblCustomerMaster.CCUST, tblCustomerMaster.CNME,
TWDXRF.XCPO, TWDHPO.PPROD, TWDHPO.PVEND, " _
& "TWDXRF.XTPO, TWDHPO.PLINE, TWDHPO.PQORD, TWDLIN.LSHQTY, TWDECH.HEDTE,
TWDHPO.ConfirmedShpDate, " _
& "TWDHPO.ScheduledDate, TWDXRF.XCORD, TWDXRF.XCUST, TWDXRF.XTVND,
tblVendorMaster.VNDNAM, TWDIIM.IDESC, " _
& "tblBuyer.DESC AS BUYER, TWDLIN.LBOL, TWDLIN.LESTDP,
tblCustomerMaster.CREF02 AS PM INTO tblTmpQuery3 " _
& "IN " & strtmp & " " & vbCrLf _
& "FROM (((((((TWDXRF LEFT JOIN TWDHPO ON TWDXRF.XTPO = TWDHPO.PORD)
LEFT JOIN tblCustomerMaster ON " _
& "TWDXRF.XCUST = tblCustomerMaster.CCUST) LEFT JOIN tblVendorMaster ON
TWDXRF.XTVND = tblVendorMaster.VENDOR) " _
& "LEFT JOIN TWDIIM ON TWDHPO.PPROD = TWDIIM.IPROD) LEFT JOIN tblBuyer
ON TWDIIM.IBUYC = tblBuyer.IPURC) " _
& "LEFT JOIN TWDLIN ON (TWDHPO.PORD = TWDLIN.LTWPO) AND
Click to expand...
(TWDHPO.PLINE
=
TWDLIN.LTWPOL)) " _
& "LEFT JOIN TWDHPH ON TWDHPO.PORD = TWDHPH.PHORD) LEFT JOIN TWDECH ON
TWDXRF.XCORD = TWDECH.HORD " _
& "ORDER BY TWDHPH.PHENDT DESC , IIf(Not
IsNull([PHENDT]),DateSerial(Left([PHENDT],4),Mid([PHENDT],5,2)," _
& "Right([PHENDT],2)),0), tblCustomerMaster.CNME, TWDXRF.XCPO,
TWDHPO.PPROD;"

'This was the line the string replaced:

'& "IN 'D:\Documents and Settings\Administrator\My Documents\Curent
Docs\Trans\Trans_Data.mdb' " _
'***************This works with it hard-coded******************

MsgBox (strsql) '*****************This look right******************

DoCmd.RunSQL strsql

set db = nothing

End Function

The path looks right from the string when I put it in a msgbox and the sql
statement looks right from the msgbox.

Any ideas?

Thanks for your help.


Douglas J Steele said:
I assume you meant that your example doesn't work... Try:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm using a frontend/backend set up with linked tables. How can I
Click to expand...
make
a
string = to the path to the backend?

strPathToBack = db.connect does work where db = currentdb.

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiuser environment ... poor performance and locks? 3
Open password protected backend 6
Multiple BE's & CurrentDB 16
Questions to multi user scenarios 1
Prevent odbc connection to the backend mdb 5
run code on backend from frontend 1
Access Access 2010 Backend DB Backup/Restore 0
Generate a new backend 2

Top