Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to search and access database problem
Hi.
I am having difficulty in figuring this out. I have an excel file that people are going to key in a part number. I created several functions that are going to be saved within the workbook that are going to query an access database for information based on that part number (i.e., cost, description, vendor, etc.). I had a sample data file that I saved as a CSV file, and I link an access table to the file. Everything was working okay. I could change the value in the CSV file, and it would automatic update in Excel. Now that I received the full data file (has more parts, has the descriptions, etc), the excel file doesn't update when loaded, and I get an intermittent windows asking me to select a data source (just hit cancel until it goes away, and then the values populate). The new data file is 5.1 meg (sample was 1.7 meg), but I had to go with a pipe ("|") delimited file since there were comas in the description (I did change the pipes to comas to see if there was a difference, but it still had the same effect). If I set Application.volitile, there is a lag time that would not be suitable (same as with the sample data file). My code is below. Since the data file location may change, I created an ini file on the server that contains the path and filename of the access database (variables path and pathlink). On open, it reads the ini file, and I originally had application.calculate which worked fine. For some reason, I have to use the calculatefull now. Right now, the main issue is the window asking for the user to select the datasource. Why am I getting that? I didn't try to import the data into access (no link), and I would prefer not to since the file is being created on a Unix server and FTPed to the working directory. Thanks in advance for any help, Brian Dim pathlink As String Dim path As String Dim varalerts As String Private Sub auto_open() On Error Resume Next Open "\\server\share\RouterLink\RouterLink.ini" For Input As #1 Input #1, pathlink Input #1, path Input #1, varalerts Close #1 If pathlink = "" Or path = "" Then MsgBox "Error loading RouterLink.ini file" Exit Sub End If If Right$(pathlink, 1) < "\" Then pathlink = pathlink & "\" varalerts = LCase(varalerts) Application.CalculateFull 'Application.Calculate End Sub Function proddesc(varcell) If varcell = "" Or IsEmpty(varcell) Or IsNull(varcell) Then Exit Function Dim Db As Database Dim Rs As Recordset Dim varquery As String 'Application.Volatile varquery = "SELECT ProductNumber, Description from Routprod1 where ProductNumber = '" & varcell & "'" Set Db = Workspaces(0).OpenDatabase(path, ReadOnly:=True) Set Rs = Db.OpenRecordset(varquery) If Not Rs.EOF Then proddesc = Rs.Fields("Description").Value Else proddesc = "Part Number: " & varcell & " was not found" End If Rs.Close Db.Close End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Search & Edit in Excel Database possible? | Excel Discussion (Misc queries) | |||
How do I create a "List If" function.I need to search a database . | Excel Worksheet Functions | |||
Problem with VBA code trying to query and access database | Excel Programming | |||
MS Access Database Connection problem in Excel XP 2002 | Excel Programming |