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

Originally I was thinking of using a macro to import the Excel *** 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 *** 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 ***? The Excel *** 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 *** 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


"Beetle" wrote:

I've imported excel data into access many times, but I haven't tried to
use a spread*** 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


"ryguy7272" wrote:

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

Excel List = name of Excel file
Main = Name of Excel *** 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


"ryguy7272" wrote:

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


"Beetle" wrote:

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


"ryguy7272" wrote:

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 ***. 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---



--
RyGuy
.

Loading