Issue with UNC Path (I think). Trying to build Dynamic Report

[ryguy7272]

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[Beetle]

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[ryguy7272]

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[ryguy7272]

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[Beetle]

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[ryguy7272]

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[Klatuu]

The simplest way to import data into an Access table from Excel is to use the
TransferSpreadsheet method. You can get the details in VBA Help.

As to UNC paths. In a multi user networked environment, you should always
use them instead of drive letter mapping.

If you have the code downloaded you referred to in your original post, it is
easy to find the UNC path for a drive litter. For example, if you want to
return the UNC path for drive R it would be:
=fGetUncPath("R:")

Also, to reverse the procedure, add this code to the module that has th
efGetUncPath function in it:

Function UNCDrive(strUNCName As String) As String
'Dave Hargis 9/05
'Returns Drive Letter for UNC drive mapping
Dim strAllDrives As String
Dim varDriveList As Variant
Dim lngDriveIndex As Long
Dim strCurrDrive As String
Dim strPathName As String

strAllDrives = fGetDrives
strAllDrives = Left(strAllDrives, Len(strAllDrives) - 1)
varDriveList = Split(strAllDrives, Chr(0))
lngDriveIndex = UBound(varDriveList) - 1
For lngDriveIndex = 0 To UBound(varDriveList)
strCurrDrive = varDriveList(lngDriveIndex)
If fDriveType(strCurrDrive) = "Network Drive" Then
strPathName = fGetUNCPath(Left(strCurrDrive, Len(strCurrDrive) -
1))
If strUNCName = strPathName Then
UNCDrive = strCurrDrive
Exit For
End If
End If
Next lngDriveIndex

End Function

If you pass it the UNC path, it will return the drive letter.

--
Dave Hargis, Microsoft Access MVP

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy


:

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[ryguy7272]

Well, that definitely gets me a little closer Klatuu. Yes, I knew about the
TransferSpreadsheet method, but with all of the other Access-related things
I'm thinking about right now, I guess I forgot about this option. Thanks for
refreshing my memory! So anyway, I created a Macro with two steps:
DeleteObject and TransferSpreadsheet. I don't know enough about this to know
if I am doing this correct, but I feel I am not. I guess the DeleteObject
deletes the entire Table and then the TransferSpreadsheet will recreate the
Table and then suck in all the data from my Excel sheet on the network drive.
This second part seems like it is working fine. I'm not sure if I should
delete that Table though. It seems like the references to this Table (such
as those links in the Form's Row Source) are getting pretty screwed up when
the Table is deleted and then recreated. Is there a way of using a Macro to
reference a Table, clear the Table, and the do the import? Also, I'm getting
the F1, F2, F3 stuff as column headers now! I think that is further screwing
up my whole process. In my Form, I have a few items in the ListBoxes in Row
Source such as:
SELECT DISTINCT tblTFI.Broker FROM tblTFI ORDER BY tblTFI.Broker;

I guess Access can't find the appropriate Column names, such as 'Broker'
because it is seeing F1, F2, etc. There must be a way around this, right.

Finally, thanks for the suggestion to use the =fGetUncPath("R:")
I'd like to try this method, however, I'm not sure where to put this
function. I know how to call a function in Excel, but in Access it must be
totally different. Can you please dispense a little more advice about how to
call this function?

Thanks for everything!!

--
RyGuy

The simplest way to import data into an Access table from Excel is to use the
TransferSpreadsheet method. You can get the details in VBA Help.

As to UNC paths. In a multi user networked environment, you should always
use them instead of drive letter mapping.

If you have the code downloaded you referred to in your original post, it is
easy to find the UNC path for a drive litter. For example, if you want to
return the UNC path for drive R it would be:
=fGetUncPath("R:")

Also, to reverse the procedure, add this code to the module that has th
efGetUncPath function in it:

Function UNCDrive(strUNCName As String) As String
'Dave Hargis 9/05
'Returns Drive Letter for UNC drive mapping
Dim strAllDrives As String
Dim varDriveList As Variant
Dim lngDriveIndex As Long
Dim strCurrDrive As String
Dim strPathName As String

strAllDrives = fGetDrives
strAllDrives = Left(strAllDrives, Len(strAllDrives) - 1)
varDriveList = Split(strAllDrives, Chr(0))
lngDriveIndex = UBound(varDriveList) - 1
For lngDriveIndex = 0 To UBound(varDriveList)
strCurrDrive = varDriveList(lngDriveIndex)
If fDriveType(strCurrDrive) = "Network Drive" Then
strPathName = fGetUNCPath(Left(strCurrDrive, Len(strCurrDrive) -
1))
If strUNCName = strPathName Then
UNCDrive = strCurrDrive
Exit For
End If
End If
Next lngDriveIndex

End Function

If you pass it the UNC path, it will return the drive letter.

--
Dave Hargis, Microsoft Access MVP

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey


:

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy


:

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to
work. If you’ve dealt with this type of issue before, and you know what to
do, please let me know.

Regards,
Ryan---

 

[ryguy7272]

I still have not gotten this thing to work. Can I use a TransferSpreadsheet
macro and preserve my Column Headings, such as Broker, Status, etc., instead
of Column headings such as F1, F2, F3, etc. Should I delete the tblTFI and
rebuild it or just reference it, clear all the data in the Table and then
import all the data from my Excel spreadsheet?

Regards,
Ryan---



--
RyGuy

Well, that definitely gets me a little closer Klatuu. Yes, I knew about the
TransferSpreadsheet method, but with all of the other Access-related things
I'm thinking about right now, I guess I forgot about this option. Thanks for
refreshing my memory! So anyway, I created a Macro with two steps:
DeleteObject and TransferSpreadsheet. I don't know enough about this to know
if I am doing this correct, but I feel I am not. I guess the DeleteObject
deletes the entire Table and then the TransferSpreadsheet will recreate the
Table and then suck in all the data from my Excel sheet on the network drive.
This second part seems like it is working fine. I'm not sure if I should
delete that Table though. It seems like the references to this Table (such
as those links in the Form's Row Source) are getting pretty screwed up when
the Table is deleted and then recreated. Is there a way of using a Macro to
reference a Table, clear the Table, and the do the import? Also, I'm getting
the F1, F2, F3 stuff as column headers now! I think that is further screwing
up my whole process. In my Form, I have a few items in the ListBoxes in Row
Source such as:
SELECT DISTINCT tblTFI.Broker FROM tblTFI ORDER BY tblTFI.Broker;

I guess Access can't find the appropriate Column names, such as 'Broker'
because it is seeing F1, F2, etc. There must be a way around this, right.

Finally, thanks for the suggestion to use the =fGetUncPath("R:")
I'd like to try this method, however, I'm not sure where to put this
function. I know how to call a function in Excel, but in Access it must be
totally different. Can you please dispense a little more advice about how to
call this function?

Thanks for everything!!

--
RyGuy

The simplest way to import data into an Access table from Excel is to use the
TransferSpreadsheet method. You can get the details in VBA Help.

As to UNC paths. In a multi user networked environment, you should always
use them instead of drive letter mapping.

If you have the code downloaded you referred to in your original post, it is
easy to find the UNC path for a drive litter. For example, if you want to
return the UNC path for drive R it would be:
=fGetUncPath("R:")

Also, to reverse the procedure, add this code to the module that has th
efGetUncPath function in it:

Function UNCDrive(strUNCName As String) As String
'Dave Hargis 9/05
'Returns Drive Letter for UNC drive mapping
Dim strAllDrives As String
Dim varDriveList As Variant
Dim lngDriveIndex As Long
Dim strCurrDrive As String
Dim strPathName As String

strAllDrives = fGetDrives
strAllDrives = Left(strAllDrives, Len(strAllDrives) - 1)
varDriveList = Split(strAllDrives, Chr(0))
lngDriveIndex = UBound(varDriveList) - 1
For lngDriveIndex = 0 To UBound(varDriveList)
strCurrDrive = varDriveList(lngDriveIndex)
If fDriveType(strCurrDrive) = "Network Drive" Then
strPathName = fGetUNCPath(Left(strCurrDrive, Len(strCurrDrive) -
1))
If strUNCName = strPathName Then
UNCDrive = strCurrDrive
Exit For
End If
End If
Next lngDriveIndex

End Function

If you pass it the UNC path, it will return the drive letter.

--
Dave Hargis, Microsoft Access MVP

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy


:

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey


:

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy


:

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data
source, sometimes pausing for several minutes, and when if finally finishes
(I’m not even sure what it is doing), I try to find my data in the drop down
menu of the ComboBox and I see nothing but blanks. After struggling with
this project for a couple of weeks, I honestly think I am close, but I’m not
exactly sure what to do at this point. Also, I think it has to do with the
UNC Path, but I’m not sure about that…I never heard of a UNC Path until
yesterday.

This is the drive that I want to save the Excel file on:
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I think somehow I have to point Access to this spot, perhaps within the code
(above). As I stated I have the Row Source pointing to the Excel file called
'Main', but Access isn't able to find the data source.

I’ve gotten some great help on this one, but I still can’t get this thing to

 

[Klatuu]

To get the field names, put Yes in the Has Field Names box in the maccro
builder.
Since you are using an macro for the TransferSpreadsheet, you are pretty
limited. You may just have to determine the UNC path, and hard code it into
your macro.

You can clear your table by creating a delete query for the table. The use
the OpenQuery action in a macro to run the query.

--
Dave Hargis, Microsoft Access MVP

I still have not gotten this thing to work. Can I use a TransferSpreadsheet
macro and preserve my Column Headings, such as Broker, Status, etc., instead
of Column headings such as F1, F2, F3, etc. Should I delete the tblTFI and
rebuild it or just reference it, clear all the data in the Table and then
import all the data from my Excel spreadsheet?

Regards,
Ryan---



--
RyGuy

Well, that definitely gets me a little closer Klatuu. Yes, I knew about the
TransferSpreadsheet method, but with all of the other Access-related things
I'm thinking about right now, I guess I forgot about this option. Thanks for
refreshing my memory! So anyway, I created a Macro with two steps:
DeleteObject and TransferSpreadsheet. I don't know enough about this to know
if I am doing this correct, but I feel I am not. I guess the DeleteObject
deletes the entire Table and then the TransferSpreadsheet will recreate the
Table and then suck in all the data from my Excel sheet on the network drive.
This second part seems like it is working fine. I'm not sure if I should
delete that Table though. It seems like the references to this Table (such
as those links in the Form's Row Source) are getting pretty screwed up when
the Table is deleted and then recreated. Is there a way of using a Macro to
reference a Table, clear the Table, and the do the import? Also, I'm getting
the F1, F2, F3 stuff as column headers now! I think that is further screwing
up my whole process. In my Form, I have a few items in the ListBoxes in Row
Source such as:
SELECT DISTINCT tblTFI.Broker FROM tblTFI ORDER BY tblTFI.Broker;

I guess Access can't find the appropriate Column names, such as 'Broker'
because it is seeing F1, F2, etc. There must be a way around this, right.

Finally, thanks for the suggestion to use the =fGetUncPath("R:")
I'd like to try this method, however, I'm not sure where to put this
function. I know how to call a function in Excel, but in Access it must be
totally different. Can you please dispense a little more advice about how to
call this function?

Thanks for everything!!

--
RyGuy

The simplest way to import data into an Access table from Excel is to use the
TransferSpreadsheet method. You can get the details in VBA Help.

As to UNC paths. In a multi user networked environment, you should always
use them instead of drive letter mapping.

If you have the code downloaded you referred to in your original post, it is
easy to find the UNC path for a drive litter. For example, if you want to
return the UNC path for drive R it would be:
=fGetUncPath("R:")

Also, to reverse the procedure, add this code to the module that has th
efGetUncPath function in it:

Function UNCDrive(strUNCName As String) As String
'Dave Hargis 9/05
'Returns Drive Letter for UNC drive mapping
Dim strAllDrives As String
Dim varDriveList As Variant
Dim lngDriveIndex As Long
Dim strCurrDrive As String
Dim strPathName As String

strAllDrives = fGetDrives
strAllDrives = Left(strAllDrives, Len(strAllDrives) - 1)
varDriveList = Split(strAllDrives, Chr(0))
lngDriveIndex = UBound(varDriveList) - 1
For lngDriveIndex = 0 To UBound(varDriveList)
strCurrDrive = varDriveList(lngDriveIndex)
If fDriveType(strCurrDrive) = "Network Drive" Then
strPathName = fGetUNCPath(Left(strCurrDrive, Len(strCurrDrive) -
1))
If strUNCName = strPathName Then
UNCDrive = strCurrDrive
Exit For
End If
End If
Next lngDriveIndex

End Function

If you pass it the UNC path, it will return the drive letter.

--
Dave Hargis, Microsoft Access MVP


:

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy


:

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey


:

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy


:

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\
UNC Path : \\AP-SVR-3\users

I can link the Excel file to a local drive, like my C-drive, and everything
works fine. However, when I put the Excel file on our firm’s network drive,
I can’t get Access to find the data source!!

I am interested in mapping my Form, or Query, or whatever to an Excel file
on the H-drive, which I guess is UNC Path : \\AP-SVR-3\users

I found the information on this site useful:
http://support.microsoft.com/kb/328440

Anyway, that’s the background. I can link the Excel file to a local drive,
like my C-drive and everything works fine. However, when I put the Excel
file on our firm’s network drive, Access seems to struggle to find the data

 

[ryguy7272]

Thank you sooooo much Klatuu!! You made my day.

You gave me a few new ideas about this thing! Although it could probably
use a few improvements, I've got it working now and that's more than what I
had last week!!

One more question. I know this is the UNC path:
\\AP-SVR-3\users

I suspect, if I implement it into my code, everything can be done in one
click.
How do I implement that path into my code?

Here is the code:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strPath As String

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

If you know, please share your knowledge. If you don't know, or don't have
time, forget it. I've got it working through the macro (and 'recorded' the
macro into a Module), and that method works quite well.


Regards,
Ryan---

--
RyGuy

To get the field names, put Yes in the Has Field Names box in the maccro
builder.
Since you are using an macro for the TransferSpreadsheet, you are pretty
limited. You may just have to determine the UNC path, and hard code it into
your macro.

You can clear your table by creating a delete query for the table. The use
the OpenQuery action in a macro to run the query.

--
Dave Hargis, Microsoft Access MVP

I still have not gotten this thing to work. Can I use a TransferSpreadsheet
macro and preserve my Column Headings, such as Broker, Status, etc., instead
of Column headings such as F1, F2, F3, etc. Should I delete the tblTFI and
rebuild it or just reference it, clear all the data in the Table and then
import all the data from my Excel spreadsheet?

Regards,
Ryan---



--
RyGuy

Well, that definitely gets me a little closer Klatuu. Yes, I knew about the
TransferSpreadsheet method, but with all of the other Access-related things
I'm thinking about right now, I guess I forgot about this option. Thanks for
refreshing my memory! So anyway, I created a Macro with two steps:
DeleteObject and TransferSpreadsheet. I don't know enough about this to know
if I am doing this correct, but I feel I am not. I guess the DeleteObject
deletes the entire Table and then the TransferSpreadsheet will recreate the
Table and then suck in all the data from my Excel sheet on the network drive.
This second part seems like it is working fine. I'm not sure if I should
delete that Table though. It seems like the references to this Table (such
as those links in the Form's Row Source) are getting pretty screwed up when
the Table is deleted and then recreated. Is there a way of using a Macro to
reference a Table, clear the Table, and the do the import? Also, I'm getting
the F1, F2, F3 stuff as column headers now! I think that is further screwing
up my whole process. In my Form, I have a few items in the ListBoxes in Row
Source such as:
SELECT DISTINCT tblTFI.Broker FROM tblTFI ORDER BY tblTFI.Broker;

I guess Access can't find the appropriate Column names, such as 'Broker'
because it is seeing F1, F2, etc. There must be a way around this, right.

Finally, thanks for the suggestion to use the =fGetUncPath("R:")
I'd like to try this method, however, I'm not sure where to put this
function. I know how to call a function in Excel, but in Access it must be
totally different. Can you please dispense a little more advice about how to
call this function?

Thanks for everything!!

--
RyGuy


:

The simplest way to import data into an Access table from Excel is to use the
TransferSpreadsheet method. You can get the details in VBA Help.

As to UNC paths. In a multi user networked environment, you should always
use them instead of drive letter mapping.

If you have the code downloaded you referred to in your original post, it is
easy to find the UNC path for a drive litter. For example, if you want to
return the UNC path for drive R it would be:
=fGetUncPath("R:")

Also, to reverse the procedure, add this code to the module that has th
efGetUncPath function in it:

Function UNCDrive(strUNCName As String) As String
'Dave Hargis 9/05
'Returns Drive Letter for UNC drive mapping
Dim strAllDrives As String
Dim varDriveList As Variant
Dim lngDriveIndex As Long
Dim strCurrDrive As String
Dim strPathName As String

strAllDrives = fGetDrives
strAllDrives = Left(strAllDrives, Len(strAllDrives) - 1)
varDriveList = Split(strAllDrives, Chr(0))
lngDriveIndex = UBound(varDriveList) - 1
For lngDriveIndex = 0 To UBound(varDriveList)
strCurrDrive = varDriveList(lngDriveIndex)
If fDriveType(strCurrDrive) = "Network Drive" Then
strPathName = fGetUNCPath(Left(strCurrDrive, Len(strCurrDrive) -
1))
If strUNCName = strPathName Then
UNCDrive = strCurrDrive
Exit For
End If
End If
Next lngDriveIndex

End Function

If you pass it the UNC path, it will return the drive letter.

--
Dave Hargis, Microsoft Access MVP


:

Originally I was thinking of using a macro to import the Excel sheet into an
Access Table. Trouble is...I don't know Access all that well (very well
versed with Excel though). Maybe that is the way to go though. How can I
use VBA to create a table in Access, and import the data from a specific
Excel sheet stored on a specific network drive? Or, how can I use a Table
that I have, clear out all data, and and import the data from a specific
Excel sheet? The Excel sheet is updated once per data, usually. And I set
up this database to allow us to create lots of dynamic Reports and Queries.
I just have to get the Excel data to flow from the Excel sheet into the
Access table. I tried this earlier in the week using code from this site:
http://www.erlandsendata.no/english/index.php?t=envbadac

Most of the things there allow you to control Access from Excel. Would I be
better off controlling Excel from Access, such as Import data from a closed
workbook into Access? Does anyone know of good, simple, VBA code for this?
Again, I am much less versed in Access than I am in Excel.

Thanks for your patience!! I will get this thing working!!

--
RyGuy


:

I've imported excel data into access many times, but I haven't tried to
use a spreadsheet as an external data source for queries, so I'm not
that familiar with it. I'll play around with that a bit on my end to see
how it works, but in the meantime, hopefully someone with more
experience ( i.e. - smarter than me ) will pick up this thread
and give you an answer.

--
_________

Sean Bailey


:

I used this:
\\AP-SVR-3\users\Excel List.xls\Main$

Excel List = name of Excel file
Main = Name of Excel sheet that I need to link to

I tried to set it up through the Linked Table Manager, and it still doesn't
work. Does the space in the file name prevent it from working?

I can actually build the Report and the Query too, but I just can not see
the data displayed in the ListBox or in the ComboBox (tried it both ways).
There is just a lot of white spaces in the ListBox and ComboBox; the data
itself seems to be invisible. But, if I click on the first item in the
ListBox, and I know what it is only because I am very familiar with the data,
that 'criteria' is what Access uses to build my dynamic Queries and my
dynamic Reports. I wish there was some way to DISPLAY those elements of data
in the ListBox...

I would graciously welcome any other ideas.


Regards,
Ryan---

--
RyGuy


:

OK, I agree. This is probably the way to go (my knowledge and experience in
this particular area is quite limited). Now, how do I use that UNC Path? Do
I put it in the Form? How? Do I use it in the code? Where? Please show me
how to implement this idea.


Regards,
Ryan---


--
RyGuy


:

UNC paths don't use drive letters, so in your case the path would look
something like;

\\AP-SVR-3\users\ExcelFiles\YourExcelFile.xls

--
_________

Sean Bailey


:

Issue with UNC Path (I think). Trying to build Dynamic Report, using SQL

Just recently I encountered a problem that I’ve never dealt with before. I
think it has to do with something called UNC Path. If you are not frightened
at this point, please read on…

I got some awesome help from this Access DG recently, and I think I am close
to resolving the issue, but I am not quite there yet, and I need a little
more of a push to get there…please bear with me.

I am trying to Link to an Excel file on a network drive and build a Report
and a Query, using a Form. Pretty simple, right (eyes rolling right now…).
Anyway, after some major frustration, I think I am pretty close to getting
this wrapped up, and I’d love to finish before I get out of here for the
weekend.

In Access, I went to File > Get External Data > Link Tables
Then, I used the Wizard to pick the right sheet. Now I have a Form with a
ComboBox that has this logic in the Row Source:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

This code is behind the Form:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

The code came from here:
http://www.fontstuff.com/access/acctut19.htm

I received some great help from the Access Programming DG and began to think
that the issue was related to something called a UNC Path. I did some
research and found the site below:
http://www.mvps.org/access/api/api0003.htm

I found that there are several UNC paths on my firm’s network. Some are as
follows:
Removable drive : A:\
Local drive : C:\
Removable drive : F:\
Network drive : H:\

 

[Klatuu]

Notes:
You don't have to turn a reports filter on and off. All you need to do is
build the string and pass it in the OpenReport, which you were already doing.

You don't need to specify the Value property of a control. It is the
default property returned.

Notice the different technique I use building the filter string. It only
adds the field to the string if the user selected it. That eliminates the
Like statements and speeds up the query.

The AddAnd function is included below your modified code. It is used to add
the AND operator only when needed.

Private Sub cmdApplyFilter_Click()
Dim strFilter As String

' Build criteria string for Broker field
If Not IsNull(Me.cboBroker) Then
strFilter = AddAnd(strFilter)
strFilter = "[Broker] = """ & Me.cboBroker & """"
End If

' Build criteria string for Product field
If Not IsNull(Me.cboProd) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[Prod] = """ & Me.cboProd & """"
End If

' Build criteria string for Status field
If Not IsNull(Me.cboStatus) Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[Status] = """ & Me.cboStatus & """"
End If

'Open The Report

DoCmd.OpenReport "SelReport", acPreview, , strFilter

End Sub

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

End IF
--
Dave Hargis, Microsoft Access MVP

Thank you sooooo much Klatuu!! You made my day.

You gave me a few new ideas about this thing! Although it could probably
use a few improvements, I've got it working now and that's more than what I
had last week!!

One more question. I know this is the UNC path:
\\AP-SVR-3\users

I suspect, if I implement it into my code, everything can be done in one
click.
How do I implement that path into my code?

Here is the code:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strPath As String

Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

If you know, please share your knowledge. If you don't know, or don't have
time, forget it. I've got it working through the macro (and 'recorded' the
macro into a Module), and that method works quite well.


Regards,
Ryan---

--
RyGuy

To get the field names, put Yes in the Has Field Names box in the maccro
builder.
Since you are using an macro for the TransferSpreadsheet, you are pretty
limited. You may just have to determine the UNC path, and hard code it into
your macro.

You can clear your table by creating a delete query for the table. The use
the OpenQuery action in a macro to run the query.