Access 2010, Web Tables/Relationships, & Excel Imports

[Coach3131]

I am interested in knowing the best practices as it relates to setting up a web database and being able to import data when the lookup field is being as a number field. I can get an import to work correctly if I leave off the lookup field, but that becomes a problem when there are a lot of rows being imported and would have to go 1 by 1 to put the data in via the combo box dropdown. Is there a way to import data and still keep relationships in a web database? I am mostly familiar with Excel and VBA, but have some client side Access background (although a little rusty) in the past, but I am now learning web database features in my spare time to enhance my skills.

 

[chrisguk]

Hi there,

Not quite sure why you quote a web database. A database is a database and not necessarily for the web or an application. It is defined as a mean of storing and extracting information efficiently without too much effort.

Web technology utilises databases through code such as server side code for example .asp, .php, java and .net.

The characteristics of a database are essentially the same in that they store data. So in essence there is no such thing as a web database.

Now depending on which format of database you are using there are various ways of ensuring data is imported successfully. for databases such as:

MySQL
Oracle
or SQL

You would use some simple SQL (sequal) commands to populate the relavant tables; for example INSERT INTO etc etc

Now for Access its a little easier. There are various resources availble that allow you to import through excel spreadsheets. But probably the easiet way is to paste the data in direct providing the table isnt linked.

I would suggest if you are using access then do some research. If you have trouble then come back for assistance.

Just to add to that. On the web you are better off using MySQL or SQL as Access is limited to the amount of users that can access the database at one time.

Chris

 

[Coach3131]

In Access 2010, it provides the ability to create a Web database. Thus, there is special rules to make a normal Access Database, Web Compliant. I am looking for best/practices to keep a database Web Compliant and be able to do Excel Imports of massive data. Basically, if I have PlayerID table with a TeamID field and want to link it with the TeamID table, in Access I can have it show me the Team name. However, if I try to import the Excel file into Access, it will give me an error if I don't have numberic value of the TeamID versus just the Team Name (which is what Access is showing in the field). I don't know if there is a way to allow imports that have text with that field to be converted to the numeric ID result very quickly.