How can I Edit and Save a Microsoft Access file without MS Access?

[vash]

I have a .mdb file that was created by our former site designer to
interface with our website ordering and product ID listing.

Now I need to add a few items to the ID list.

I do not have Microsoft Access but I do have I think Microsoft Small
Business 2002 (which is basically MS Word and Excel 2002).

Now I do know I can view the database within MS Excel 2002. When I open
the .mdb file I am presented with a "Select Table" box that contains 4
table options, 2 query customer group and another table of customer
data and then the fourth option which is the product ID table.

So now I know I can at least view these 4 different tables in Excel.
Now my problem is, when I choose the product ID table at the "Select
Table" box that immediately requests me to pick one when I initially
open the .mdb file ...

My problem is a two parter:
1) The question is it doesn't allow me to save it into a .mdb file only
..html and excel format files, is there a work around for this? If not
am I forced to essentially buy MS Access to edit and save this .mdb
file?

2) If I am somehow able to save this with Excel, will it only save the
current table I chose or would it damage and overwrite the other 3
tables, possibly reducing my 4 Table database into 1 (ie. losing the
customer information tables).

Thank you in advance.

 

[Jeff Boyce]

Your designer used MS Access to create the table structure.

You have to use MS Access to edit the table structure.

You wouldn't expect to use MS Excel to modify a Word document, would you?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Hate to argue, Jeff, but he should be able to make table changes from Excel
using VBA.

It should only be necessary to set a reference to DAO or ADOX, then any
desired table change should be possible.

vash: give some more details about what you're trying to do.

 

[Albert D. Kallal]

It turns out that windows ships with the JET database engine (the same one
that ms-access uses).

So, what this means is that you can actually write windows scripts to read,
or write, or even modify the mdb tables.

Set dbeng = CreateObject("DAO.DBEngine.36")
strMdbFile = "C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb"
Set db = dbeng.OpenDatabase(strMdbFile)
strQuery = "select * from contacts"
Set rs = db.OpenRecordset(strQuery)
rs.movefirst
If rs.EOF = true Then
quit
End If

strTextOut = "C:\t5.txt"
set fs = Wscript.CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile(strTextOut, 2, True)
' 2 = write, 1 = read

do while rs.EOF = false
strOutText = rs("LastName")
ts.Writeline strOutText
rs.movenext
loop
ts.close
rs.close

You can paste the above into notepad, and re-name it with a vbs
extension..and away you go. So, you don' need ms-access here. You can use
any programming language you have, including the built in windows scripting
to read/write those mdb files...

 

[Zac Woodall [MSFT]]

An MDB is just a Jet database. Any ODBC data aware app which includes
writing support should be able to write for you. If you want to have a
little bit of an adventure, and you have Admin access on your machine,
install the free Beta 2 TR release of Office 2007 for free. It includes
Access.

http://www.microsoft.com/office/preview/beta/getthebeta.mspx

Thanks!
Zac Woodall | Program Manager | Microsoft Access

 

[vash]

Keep in mind I don't know anything about databases, at least the
complexities of its operation.

What is VBA?

Basically here it is step by step.
1. I open the .mdb file.
2. "Select Table" Pop up appears right after I open it with 4
selectable options 2 "Cust query", 1 Cust which all house customer and
orders in the database, then the final table option to select is the
Product one.
3. I click Product table and it opens the Product listings only, it
does not open the other 3 tables (Customer information database). This
newly opened product spreadsheet is presented in a 4 column excel
spreadsheet and around 28 rows (which represent the number of items).
The columns represent, Product ID, item ID, Item name and Item Price.
Simple enough so far.
4. So at this point, what I'm trying to do is add a few more rows with
new items and fill in the corresponding columns for example:
NewID20, ItemID20, NewItemName20, 5
NewID21, ItemID21, NewItemName21, 10
ect.

I want to essentially put these in their proper columns and save the
entire database without affecting the other 3 sections (the 3 Customer
information tables).

Again my main inquiry is whatever I do must not damage, split up or
delete the other 3 Cust query tables section of this database.

Do I need MS Access for this? If so is there any kind of upgrade path
for me? I purchased MS Office Pro 95 and what I'm using now MS Office
(Small Business 2002).

Thank you in advance everyone,

 

[vash]

Albert, I tried this and for the strmdbfile= part, I changed the path
to my .mdb file (I was under the assumption I was supposed to do that
since I got an error when I pasted as is. After the edit, I get this
error "The Microsoft Jet database engine cannot find the input table or
query 'contacts'. Make sure it exists and that its name is spelled
correctly."

 

[Larry Linson]

Hate to argue, Jeff, but he should be able to make
table changes from Excel using VBA.

Surely you can, but from a practical point of view, even if the O.P. were
experienced with VBA (and if that were so, likely would not have to ask the
question), it would be far less expensive in time and effort, to simply
obtain a copy of Access and modify the tables via the user interface.

Larry Linson
Microsoft Access MVP

 

[Douglas J. Steele]

Surely you can, but from a practical point of view, even if the O.P. were
experienced with VBA (and if that were so, likely would not have to ask
the question), it would be far less expensive in time and effort, to
simply obtain a copy of Access and modify the tables via the user
interface.

From his follow-up post, it sounds like all he wants is to add new data to
an existing table, not change the design of it. However, it also sounds as
though you're correct, and he doesn't know VBA.

I know that it's possible to update Access from Excel, but I've never done
it myself.

 

[Douglas J. Steele]

Albert's code was meant as a sample only. You need to determine what the
actual name of your table is, and replace "contacts" with that name. If you
choose Data | Import External Data | New Database Query from the Excel menu,
you'll be able to drill down into the Jet database and determine the correct
table and field names. Unfortunately, I'm not sure whether it's possible to
use menu choices to update Access from Excel.

Are you familiar with creating what Excel calls Macros (which are VBA code,
as opposed to what Access calls Macros)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Albert, I tried this and for the strmdbfile= part, I changed the path
to my .mdb file (I was under the assumption I was supposed to do that
since I got an error when I pasted as is. After the edit, I get this
error "The Microsoft Jet database engine cannot find the input table or
query 'contacts'. Make sure it exists and that its name is spelled
correctly."

 

[Albert D. Kallal]

I get this

error "The Microsoft Jet database engine cannot find the input table or
query 'contacts'. Make sure it exists and that its name is spelled
correctly."

Well, do you have a table or query called contacts? It would quite the
miracle indeed if I could read your mind, and have correctly guessed the
name of your table, and it is called contacts.

I think that error message is quite clear...perhaps so clear, that you DO
ACTUALLY have a table called contacts?

Is there anything about that message that is not clear? That message says:


"The Microsoft Jet database engine cannot find the input table "

so, what is the name of your table? Perhaps you don't realize that just like
a excel document can have many workbooks, a mdb file can have MANY MANY
tables, and in fact there is *usually* a number of system tables in that
list. So, you going to have to name the table. (you must have asked what
"contacts" was in my sample script???

Remember, the database file can have many tables.....

You could list all of the tables with:

Set dbeng = CreateObject("DAO.DBEngine.36")
strMdbFile = "C:\Documents and Settings\Lawrence\My
Documents\Access\ScriptExample\MultiSelect.mdb"
Set db = dbeng.OpenDatabase(strMdbFile)
for each table in db.tabledefs
msgbox table.name
next

You could also write the above list out to a text file if you use the file
scripting object.

 

[Arvin Meyer [MVP]]

Unfortunately, I'm not sure whether it's possible to use menu choices to
update Access from Excel.

Actually, it is possible to edit an Access table from Excel. Works quite
nicely too if you don't have Excel.

 

[Douglas J. Steele]

Actually, it is possible to edit an Access table from Excel. Works quite
nicely too if you don't have Excel.

I assume you mean "Works quite nicely too if you don't have Access"

Is this something that can be accessed (no pun intended) from the menu in
Excel, though, or is some coding required?

 

[Arvin Meyer [MVP]]

Yes I meant Access. I only had 1 cup of coffee and the cobwebs were still
there. Yes, From the file menu choose Open then in the resulting dialog,
change the files of type to Access Databases or All Data Sources, either of
which will al;low you to read and MDB or MDE.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com