Get data from Access file

mjones · Feb 6, 2010

HI All,

Is it possible to populate a cell from a cell (if that’s what it’s
called) in an Access file?

The access file is called qcpProg.mdb in a directory level one above
the excel file.

The data is in the tProduct table, UnitCost field (currency), with the
Product Code field (text) that is equal to Joseph4.

It would save me a lot of manual updates if I could do this.

Thanks!

Michele

dan dungan · Feb 6, 2010

Hi Michele,

In excel 2000, I use Data> Get External Data> New Database query.

Would that work in your case?

Dan

Tim Williams · Feb 6, 2010

Try this. You'll need to add a reference to the ADO library in your VB
Project (whichever is the latest version you have should be fine)
Adjust DB path to suit.

Tim

'*******************************************
Function GetUnitCost(ProdCode)

Const DB_PATH As String = "C:\Stuff\qcpProg.mdb"
Dim retVal As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DB_PATH & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost= retVal
End Function
'*********************************************

HI All,

Is it possible to populate a cell from a cell (if that’s what it’s
called) in an Access file?

The access file is called qcpProg.mdb in a directory level one above
the excel file.

The data is in the tProduct table, UnitCost field (currency), with the
Product Code field (text) that is equal to Joseph4.

It would save me a lot of manual updates if I could do this.

Thanks!

Michele

mjones · Feb 7, 2010

Try this. You'll need to add a reference to the ADO library in your VB
Project (whichever is the latest version you have should be fine)
Adjust DB path to suit.

Tim

'*******************************************
Function GetUnitCost(ProdCode)

Const DB_PATH As String = "C:\Stuff\qcpProg.mdb"
Dim retVal As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sql As String

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "DataSource=" & DB_PATH & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost= retVal
End Function
'*********************************************

HI All,

Is it possible to populate a cell from a cell (if that’s what it’s
called) in an Access file?

The access file is called qcpProg.mdb in a directory level one above
the excel file.

Thedatais in the tProduct table, UnitCost field (currency), with the
Product Code field (text) that is equal to Joseph4.

It would save me a lot of manual updates if I could do this.

Thanks!

Michele

Hi Tim,

I very much appreciate your help. I'm a smart person, but not really
a programmer so I'll need a bit more help to get this working. Once I
can do it once, I'm sure I can easily repeat it.

1 - I don't know what you mean by 'add a reference to the ADO library
in your VB Project'. I'm using Office version 2003. I thought you
might mean Data > Import External Data, but when I try to import the
file and tProduct table, it complains that I don't have read
permission. I'm really not sure if I'm in the right place anyway.

2 - I changed - Const DB_PATH As String = "C:\Stuff\qcpProg.mdb" to
Const DB_PATH As String = "..\qcpProg.mdb". Is that right? Because
several people access this file on the network, we can't use c:\. For
everyone, the access file is one directory above the excel file so I
thought the relative reference of ..\ might work.

I do hope you respond and that what I'm asking is not too difficult.
I can carefully follow step by step instructions and hope you or
someone can help.

Thank you all,

Michele

Tim Williams · Feb 8, 2010

Michele,

See my replies to your questions below.

Copy the new code to a general module in the VBEditor.

You can then use the function on a worksheet like a normal Excel function

Eg. in B1:

=GetUnitCost(A1)

where A1 contains the product code.

Tim

Hi Tim,

I very much appreciate your help. I'm a smart person, but not really
a programmer so I'll need a bit more help to get this working. Once I
can do it once, I'm sure I can easily repeat it.

1 - I don't know what you mean by 'add a reference to the ADO library
in your VB Project'. I'm using Office version 2003. I thought you
might mean Data > Import External Data, but when I try to import the
file and tProduct table, it complains that I don't have read
permission. I'm really not sure if I'm in the right place anyway.

**************
I edited my original code (see below) so you don't need this now.
**************

2 - I changed - Const DB_PATH As String = "C:\Stuff\qcpProg.mdb" to
Const DB_PATH As String = "..\qcpProg.mdb". Is that right? Because
several people access this file on the network, we can't use c:\. For
everyone, the access file is one directory above the excel file so I
thought the relative reference of ..\ might work.

*********************************
I just included "C:\Stuff\qcpProg.mdb" as an example - I assumed you would
change it to suit your environment.
The version below worked for me using the relative path.

Function GetUnitCost(ProdCode)

Const DB_PATH As String = "..\qcpProg.mdb"
Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DB_PATH & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal
End Function

**********************************

I do hope you respond and that what I'm asking is not too difficult.
I can carefully follow step by step instructions and hope you or
someone can help.

Thank you all,

Michele

mjones · Feb 9, 2010

Michele,

See my replies to your questions below.

Copy the new code to a general module in the VBEditor.

You can then use the function on a worksheet like a normal Excel function

Eg. in B1:

=GetUnitCost(A1)

where A1 contains the product code.

Tim

I very much appreciate your help. I'm a smart person, but not really
a programmer so I'll need a bit more help to get this working. Once I
can do it once, I'm sure I can easily repeat it.

1 - I don't know what you mean by 'add a reference to the ADO library
in your VB Project'. I'm using Office version 2003. I thought you
might mean Data > Import External Data, but when I try to import the
file and tProduct table, it complains that I don't have read
permission. I'm really not sure if I'm in the right place anyway.

**************
I edited my original code (see below) so you don't need this now.
**************

2 - I changed - Const DB_PATH As String = "C:\Stuff\qcpProg.mdb" to
Const DB_PATH As String = "..\qcpProg.mdb". Is that right? Because
several people access this file on the network, we can't use c:\. For
everyone, the access file is one directory above the excel file so I
thought the relative reference of ..\ might work.

*********************************
I just included "C:\Stuff\qcpProg.mdb" as an example - I assumed you would
change it to suit your environment.
The version below worked for me using the relative path.

Function GetUnitCost(ProdCode)

Const DB_PATH As String = "..\qcpProg.mdb"
Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DB_PATH & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal
End Function

**********************************

I do hope you respond and that what I'm asking is not too difficult.
I can carefully follow step by step instructions and hope you or
someone can help.

Thank you all,

Michele

Hi,

I tried what your new code. I'm getting #Value. Could it be because
my database file has a password?

By the way, I put your code in a module, not as an object in a sheet.
Is that right? The object returned #Name? error.

Thanks again for any help you can provide. It would be great to get
this working.

Michele

Tim Williams · Feb 9, 2010

A regular module is the correct place for the code.
Here's a version with the ability to provide a password.
Just set DB_PW to the correct value.

I also modified the code to get the path to the DB. When I tried it again
today it wasn't working, I think becuase My "current directory" was
different.

Tim

'**********************************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPassword"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & _
";Jet OLEDB

atabase Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal
End Function
'***************************************

Tim

Michele,

See my replies to your questions below.

Copy the new code to a general module in the VBEditor.

You can then use the function on a worksheet like a normal Excel function

Eg. in B1:

=GetUnitCost(A1)

where A1 contains the product code.

Tim

I very much appreciate your help. I'm a smart person, but not really
a programmer so I'll need a bit more help to get this working. Once I
can do it once, I'm sure I can easily repeat it.

1 - I don't know what you mean by 'add a reference to the ADO library
in your VB Project'. I'm using Office version 2003. I thought you
might mean Data > Import External Data, but when I try to import the
file and tProduct table, it complains that I don't have read
permission. I'm really not sure if I'm in the right place anyway.

**************
I edited my original code (see below) so you don't need this now.
**************

2 - I changed - Const DB_PATH As String = "C:\Stuff\qcpProg.mdb" to
Const DB_PATH As String = "..\qcpProg.mdb". Is that right? Because
several people access this file on the network, we can't use c:\. For
everyone, the access file is one directory above the excel file so I
thought the relative reference of ..\ might work.

*********************************
I just included "C:\Stuff\qcpProg.mdb" as an example - I assumed you would
change it to suit your environment.
The version below worked for me using the relative path.

Function GetUnitCost(ProdCode)

Const DB_PATH As String = "..\qcpProg.mdb"
Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DB_PATH & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal
End Function

**********************************

I do hope you respond and that what I'm asking is not too difficult.
I can carefully follow step by step instructions and hope you or
someone can help.

Thank you all,

Michele

Hi,

I tried what your new code. I'm getting #Value. Could it be because
my database file has a password?

By the way, I put your code in a module, not as an object in a sheet.
Is that right? The object returned #Name? error.

Thanks again for any help you can provide. It would be great to get
this working.

Michele

mjones · Feb 10, 2010

A regular module is the correct place for the code.
Here's a version with the ability to provide a password.
Just set DB_PW to the correct value.

I also modified the code to get the path to the DB. When I tried it again
today it wasn't working, I think becuase My "current directory" was
different.

Tim

'**********************************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPassword"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & _
";Jet OLEDBatabase Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal
End Function
'***************************************

Tim

Hi,

I tried what your new code. I'm getting #Value. Could it be because
my database file has a password?

By the way, I put your code in a module, not as an object in a sheet.
Is that right? The object returned #Name? error.

Thanks again for any help you can provide. It would be great to get
this working.

Michele

Hi Again,

I used the new code, replacing myPassword with the actual password,
but I'm still getting #Value!. Do I need to include the logon name as
well as the password? The name is michele.

I'm not sure how the code knows how to go up a directory level, but I
trust your code is correct because I'm unable to decipher it.

I'm using Excel 2003 and Access 2003. The access table is tProduct
and in table design view the field name ProductCode is text and the
UnitCost field is currency. PMBOK4 is In Excel cell B47 and the
UnitCost in Access for the record with PMBOK4 is $34.07. The goal is
for $34.07 to appear in Excel in cell C45. I think that C45 is
supposed to contain =GetUnitCost(B47). Is that right?

Now my friend is trying to do this, too, so you will help us both.

Thanks again,

Michele

mjones · Feb 11, 2010

Hi Again,

I used the new code, replacing myPassword with the actual password,
but I'm still getting #Value!. Do I need to include the logon name as
well as the password? The name is michele.

I'm not sure how the code knows how to go up a directory level, but I
trust your code is correct because I'm unable to decipher it.

I'm using Excel 2003 and Access 2003. The access table is tProduct
and in table design view the field name ProductCode is text and the
UnitCost field is currency. PMBOK4 is In Excel cell B47 and the
UnitCost in Access for the record with PMBOK4 is $34.07. The goal is
for $34.07 to appear in Excel in cell C45. I think that C45 is
supposed to contain =GetUnitCost(B47). Is that right?

Now my friend is trying to do this, too, so you will help us both.

Thanks again,

Michele

Hi All,

I tried to stepped through the code and it times out when it tries to
connect to the database. It is looking for the database in the
correct folder. Any hints would be great!

Thanks again,

Michele

Tim Williams · Feb 11, 2010

It "works for me" - without your exact files it's difficult for me to say
why it's not working for you.

Maybe you're using a different Access security setup ?
Yet another version below, this time with an error handler to report any
error out to the function output.

If you still can't get it to work I don't know what else to suggest other
than send me the files and I'll take a look.

I'm at gmail under timjwilliams

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPasswordx"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

On Error GoTo haveError

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbPath & _
";Jet OLEDB

atabase Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

Hi Again,

I used the new code, replacing myPassword with the actual password,
but I'm still getting #Value!. Do I need to include the logon name as
well as the password? The name is michele.

I'm not sure how the code knows how to go up a directory level, but I
trust your code is correct because I'm unable to decipher it.

I'm using Excel 2003 and Access 2003. The access table is tProduct
and in table design view the field name ProductCode is text and the
UnitCost field is currency. PMBOK4 is In Excel cell B47 and the
UnitCost in Access for the record with PMBOK4 is $34.07. The goal is
for $34.07 to appear in Excel in cell C45. I think that C45 is
supposed to contain =GetUnitCost(B47). Is that right?

Now my friend is trying to do this, too, so you will help us both.

Thanks again,

Michele

Hi All,

I tried to stepped through the code and it times out when it tries to
connect to the database. It is looking for the database in the
correct folder. Any hints would be great!

Thanks again,

Michele

mjones · Feb 11, 2010

It "works for me" - without your exact files it's difficult for me to say
why it's not working for you.

Maybe you're using a different Access security setup ?
Yet another version below, this time with an error handler to report any
error out to the function output.

If you still can't get it to work I don't know what else to suggest other
than send me the files and I'll take a look.

I'm at gmail under timjwilliams

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPasswordx"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

On Error GoTo haveError

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbPath & _
";Jet OLEDBatabase Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

Hi All,

I tried to stepped through the code and it times out when it tries to
connect to the database. It is looking for the database in the
correct folder. Any hints would be great!

Thanks again,

Michele

It says 'record(s) cannot be read; no read permission on tProduct.'

Tim Williams · Feb 11, 2010

It says 'record(s) cannot be read; no read permission on tProduct.'

....then you likely have some different type of security set up in Access.
I'm not a big Access user, so I can't offer much help there.

You could try some of the other connection string listed here for Access:
http://www.connectionstrings.com/access
Look at the strings for "Microsoft Jet OLE DB 4.0" The one I used was
"With database password".

Tim

mjones · Feb 12, 2010

...then you likely have some different type of security set up in Access.
I'm not a big Access user, so I can't offer much help there.

You could try some of the other connection string listed here for Access:http://www.connectionstrings.com/access
Look at the strings for "Microsoft Jet OLE DB 4.0" The one I used was
"With database password".

Tim

Wow, this is frustrating. I tried some of the options on Tim's link,
but I'm not skilled enough to understand what to do. There is a user
(michele) and a workgroup system file (qcpSystem.mdw in the same
folder as the access database). Can anyone help with the updated code
to add this information?

I had a third friend look at it (an Access programmer) and she didn't
know what the problem was either.

Is Option Explicit supposed to be at the top of the module?

I tried copying the code into a blank spreadsheet. Same thing. Sign.

Thanks again everyone and anyone who can help,

Michele

Tim Williams · Feb 12, 2010

OK this is my last try. If you still can't get it to work, you need to send
the files.

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPasswordx"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

On Error GoTo haveError

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

' conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & dbPath & _
' ";Jet OLEDB

atabase Password=" & DB_PW & ";"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & ";Jet OLEDB:System Database=qcpSystem.mdw;" & _
"User ID=michele;Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

...then you likely have some different type of security set up in Access.
I'm not a big Access user, so I can't offer much help there.

You could try some of the other connection string listed here for
Access:http://www.connectionstrings.com/access
Look at the strings for "Microsoft Jet OLE DB 4.0" The one I used was
"With database password".

Tim

Wow, this is frustrating. I tried some of the options on Tim's link,
but I'm not skilled enough to understand what to do. There is a user
(michele) and a workgroup system file (qcpSystem.mdw in the same
folder as the access database). Can anyone help with the updated code
to add this information?

I had a third friend look at it (an Access programmer) and she didn't
know what the problem was either.

Is Option Explicit supposed to be at the top of the module?

I tried copying the code into a blank spreadsheet. Same thing. Sign.

Thanks again everyone and anyone who can help,

Michele

mjones · Feb 12, 2010

OK this is my last try. If you still can't get it to work, you need tosend
the files.

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const DB_PW As String = "myPasswordx"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim dbPath As String

On Error GoTo haveError

dbPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\")) & _
DB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

' conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & dbPath & _
' ";Jet OLEDBatabase Password="& DB_PW & ";"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & ";Jet OLEDB:System Database=qcpSystem.mdw;" & _
"User ID=michele;Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

Wow, this is frustrating. I tried some of the options on Tim's link,
but I'm not skilled enough to understand what to do. There is a user
(michele) and a workgroup system file (qcpSystem.mdw in the same
folder as the access database). Can anyone help with the updated code
to add this information?

I had a third friend look at it (an Access programmer) and she didn't
know what the problem was either.

Is Option Explicit supposed to be at the top of the module?

I tried copying the code into a blank spreadsheet. Same thing. Sign..

Thanks again everyone and anyone who can help,

Michele

Okay, Tim. Thanks for trying again. Much appreciated. I'll send the
file tomorrow because I have to reduce it considerably being that it's
my company's accounting file ... and it's 1 in the morning here.

The last try gives - "Cannot start your application. The workgroup
information file is missing or opened exclusively by another user."
Currently, this computer is the only one on (with only one user).

Good night.

Tim Williams · Feb 12, 2010

mjones said:
Okay, Tim. Thanks for trying again. Much appreciated. I'll send the
file tomorrow because I have to reduce it considerably being that it's
my company's accounting file ... and it's 1 in the morning here.

The last try gives - "Cannot start your application. The workgroup
information file is missing or opened exclusively by another user."

Currently, this computer is the only one on (with only one user).

Good night

Forgot to adjust the "qcpSystem.mdw" path to allow for it being one level
up....
That might fix it.

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const SYSDB_NAME As String = "qcpSystem.mdw"
Const DB_PW As String = "myPassword"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim parentPath As String
Dim dbPath As String, sysdbPath As String

On Error GoTo haveError

parentPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\"))

dbPath = parentPath & DB_NAME
sysdbPath = parentPath & SYSDB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & ";Jet OLEDB:System Database=" & _
sysdbPath & ";User ID=michele;" & _
"Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

mjones · Feb 12, 2010

Forgot to adjust the "qcpSystem.mdw" path to allow for it being one level
up....
That might fix it.

Tim

'*******************************
Function GetUnitCost(ProdCode)

Const DB_NAME As String = "qcpProg.mdb"
Const SYSDB_NAME As String = "qcpSystem.mdw"
Const DB_PW As String = "myPassword"

Dim retVal As String
Dim conn As Object
Dim rst As Object
Dim sql As String
Dim parentPath As String
Dim dbPath As String, sysdbPath As String

On Error GoTo haveError

parentPath = Left(ThisWorkbook.Path, _
InStrRev(ThisWorkbook.Path, "\"))

dbPath = parentPath & DB_NAME
sysdbPath = parentPath & SYSDB_NAME

retVal = "NoProdCodeSupplied"

If Len(ProdCode) > 0 Then
sql = "select UnitCost from tProduct where " & _
" ProductCode = '" & ProdCode & "'"
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
dbPath & ";Jet OLEDB:System Database=" & _
sysdbPath & ";User ID=michele;" & _
"Password=" & DB_PW & ";"

Set rst = conn.Execute(sql)
If Not rst.EOF Then
retVal = rst.Fields(0).Value
Else
retVal = "UnknownProdCode!"
End If

rst.Close
conn.Close
End If

GetUnitCost = retVal

Exit Function

haveError:
GetUnitCost = Err.Description

End Function

Yeah, yeah!!! It works!! Yeah, yeah!!

Thanks a bunch!!

Tim Williams · Feb 13, 2010

mjones said:
Yeah, yeah!!! It works!! Yeah, yeah!!
Thanks a bunch!!

At last! Good to hear.

Tim

mjones · Feb 13, 2010

At last! Good to hear.

Tim

Hi Tim,

I hate to bug you again, but is there a way to make the GetUnitCost
keep the format of the cell it's in, or at least format it to a number
or currency with 2 decimal places? Some of the costs are coming out
with 4 decimal places.

Thanks again,

Michele

Tim Williams · Feb 16, 2010

Sorry - a formula can't directly impose any formatting on the cell which
contains it: it can only return a value.

You'll need to format the cell the usual way.

Tim

At last! Good to hear.

Tim

Hi Tim,

I hate to bug you again, but is there a way to make the GetUnitCost
keep the format of the cell it's in, or at least format it to a number
or currency with 2 decimal places? Some of the costs are coming out
with 4 decimal places.

Thanks again,

Michele

Get data from Access file

mjones

dan dungan

Tim Williams

mjones

Tim Williams

mjones

Tim Williams

mjones

mjones

Tim Williams

mjones

Tim Williams

mjones

Tim Williams

mjones

Tim Williams

mjones

Tim Williams

mjones

Tim Williams