excel locked if linked to access table.

Hi, I have an access 2007 database table linked to an excel spreadsheet. however, if the database is opened, I cannot open the excel spreadsheet. it says the file is in use. If i open the excel spreadsheet before i open the database, the file doesn't allow me to save. I want to allow users to access the database (not modifying), while I could be updating the spreadsheet. Is this possible? I have researched the issue and found no solutions.
these are stored in a network.

thanks in advance.


Post your answer or comment

comments powered by Disqus
Hi

I am trying to link to Access 2007 data in Excel 2007 using usual "Get External Data - From Access" route. Specifically I am wanting to link to certain Select queries.

However, whilst Excel 2007 is apparently able to link to Queries and Tables, only certain queries are given as options to connect to. I am not sure why the vast majority of queries are not available and the ones that are available are also select queries and are not less complicated (In fact, quite the reverse, with embedded queries etc. and very simple queries on tables with single criteria are not shown as options.)

I am at a loss to explain this. If anyone has any suggestions, would really appreciate it.

Thanks
Mike

Hi All,

I want to Import Excel file data in Access Table on Submit Button click through code. I would appreciate if you could help me in this reqard. Please find the enclosed files for the reference.

In this file export button is working fine. But I am confused in
" How to write a code in on click button to import the excel data to access table automatically.


Need urgent help.

Santosh

I am new to access.

How do I create a from that can up date data linked to multiple tables.

I'm using Access and Excel 2007

I know how to import an Excel spreadsheet as a table.

I have several supplier price lists in Excel.

I want to keep my vendor price lists up to date.

When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.

I believe the form would look like:

Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required.
Field: "OEM" (Key Field found in each table) Required.
Field: "Brand" (Field found in each table) Not required.
Field: "Price" (Field found in each table) Required.

OEM would be the unique key field.

If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes.
Or I could enter new data in that vendor price list.

Thanks for your help.

Matt

Hi All,

I have a problem linking to tables in an external Access database.

I have to run through a loop potentially 1000’s of times, and each time my code needs to link to an external table (depending on certain criteria), extract the data and manipulate it. Then my loop deletes the link and begins the whole process again. The code that I’m using to create the link is as follows:


	Code:
	 
appAccess.DoCmd.TransferDatabase acLink, "Microsoft Access", strExternalDB, acTable, strTableName, "tblData"

The problem occurs initially at the beginning of the loop when I get a debug saying “Run-time error 3045. Could not use pathtodatabasedb.mdb; file already in use.” However, all I need to do after this debug is press continue or F5 etc, and the code continues running perfectly for the rest of the loop, without me having made any changes.

I have thought about incorporating some error handling, for example, that if Err.Number = 3045 then attempt to re-link again but this has been to no avail.

If it makes any difference – not that I think it does – it is actually already an external database (appAccess in my code snippet above) that is linking to external tables in yet another database. I.e. My main database (DB1) is running code and manipulating data in a secondary database (DB2) which is then linking to tables in a storage database (DB3).

Does anyone have any ideas as how to solve this issue?

With additional development it would be possible for me to have the all the data in one table (with potentially 100,000’s records) and then only need to link to it once. However, this would result in a loss of performance as my SQL queries would take far longer to extract the data because they would contain more “WHERE” clauses and would need to go through all the records finding the relevant data. Therefore, this is hopefully not a viable option.

Hi

Can anyone advise me on code which would create a link to a access table using ODBC. I need this as the database is on a network.

Thanks in advance....

Access 2003 linked to SQL Server form all working fine except for a linking to a table that includes bit fields.

I've managed to narrow the problem down to how the link handles bit fields.

If the query on which the form is linked contains bit fields then records cannot be deleted and an error is thrown.

If I use a query on the same tables but only select non bit fields there's not a problem.

Is there a data type that I can change the SQL Server Table Field to that will still allow Access to interpret the field for true / false fields but not throw errors when I delete??

Thanks in advance

hai friends

if any one uploads the excel file with 3 colums filled up and then uploads the .xls file to my site .... in my site i have to read the xls file and convert the data to a new table or to a table with existing records.
please help me in doing this...
ramesh.s

I need help to convert the below excel if statement to access.
=IF(AND(H6>M6),H6-M6)

I did the convertion but it is not working properly, please find below my convertion

Awaiting Outcome: IIf([Number New referrals]>[Total Outcome],[Number new referrals]-[Total Outcome])

I have a form that I created in Microsoft Word 2003. I need the field on the form to link to a table in Microsoft Access 2003. Everytime a new form is created I want to be able to run a Macro which will populate the Access table with the information. Any ideas?

I am outputting queries to access tables in an an external database ( new tables created when the export is done) and running into problems with autonumber fields, ie it trying to create more than one for the destination table.

Is there a way I can just output the data as is - ignoring that fact that any fields may be autonumbers??

Currently I have to output to excel and then import again - which defeats the purpose of me putting it to mdb in the first place.


Thanks!

I wonder if you can help. I am trying to make a combo box with peoples ID numbers in which links to a table with that persons details. I have made the combo box with the names in but now trying to link the table to it.
For example: Fred is in the combo box with ID number 1 and the user selects him and wants to see his details about him after clicking continue. The form has autonumber 1 showing Fred's details after clcking this.
Any help
Cheers
John

Hi
As I am fairly new to Access DB, I would be grateful for some help on an access DB that I created. The database function reasonably well and in the original version I had subforms attached to main forms to complete joins. The problem is that I seem to have too many individual forms which would complicates matters for data entry and you would have to input data in a certain order.
I would like to have a main form where almost everything is inputted but I don’t fully understand the concept of having a main form which is linked to several tables. Is this possible and how is it achieved. There are two scenarios. Thanks in advance for any advice. I have attached a copy of the DB.


1)A Vehicle Stop is Conducted (Vehicle Stop Table), Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Driver Detail Inputted (Driver Table). The Driver, Vehicle and Employee might already be in the system, having being encountered before.

2)An Enforcement Visit is made to either a Company or LabUs (Enforcement Visit Table). Company (Company Table) and LabUs (LabUs Table) inputted. Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Driver Detail Inputted (Driver Table). The Company, LabUs, Driver, Vehicle and Employee might already be in the system, having being encountered before. Attached Files oldboy 7222.zip (311.8 KB, 6 views) Reply With Quote 06-10-2010, 04:27 PM #2 llkhoutx Competent Performer Windows Vista Access 2007 Join Date Jan 2010 Location Houston, Texas USA Posts 340 Building a query to display all the field you specify on one form will create a world of grief for you in that you'll probably end up with a nonupdatable recordset, that is, new records cannot be ctreaed nor will existing records be updatable.

For starters:
1. Create relationships for among all you tables with primary and foreign keys.
2. Your main form should have your primary table as it's recordsource.
3. Other tables should be one or more related subforms on the main form.
4. One or more subsubforms, a (subsub) form related to a subform, may be applicable.

Note that forms may be relationally nested 3 deep easily. Any deeper nesting required added code.

Hi guys

I have developed a project in access. When the user press send button then it extracts records from access table and transfer in the excelsheet and that excelsheet is then sent to an email address using outlook.

And now when the user receives the email then in the excelsheet some columns are empty to let the user enter some more information in the corresponding records. When the information is being entered and sent back to the original sender manually.

Now from that excelsheet, I want some facility to transfer the new information of those records back to access table. Like it will transfer the extra added information in some columns which were originally empty back to the access table whose ID matches with the ID in excelsheet.(ID is the unique field)

Can anyone please guide me how to do that.

Thanks

I have a combo box on my form called "Location" that I would like to link to two tables.

Table 1 is a local suburb/towns list
Table 2 is a countries list

I would then like to tick a box to determine whether the location is local or overseas, and have the combo box give me the correct table

Any ideas?

I would like to use Access as a source for my pivot tables as i am trying to build a pivot table based on 10 different excel sheets. so I performed the following:

1. I linked the excel sheets into Access
2. created a Union Query between the two tables in access
3. created a pivot table in excel based on external data source
a. clicked Pivot table wizard
b. External data source
c. Get data
d. Tab: Databases, I clicked on
E. Named the query and selected a driver: "Driver do Microsoft Access (*.mdb)
F. Then clicked on "Connect" in navigated to the Access data base then click OK then another OK
G. in the tab "Databases" you'll find the new created data source highlighted it and click OK
H. pick the Union query in the list and click on ">" then click OK
4. Now i have a pivot table in excel based on access as an external data source.

the issue is when the excel sheet linked to access, the column including "No of products" formatted as number in execl in access automatically set to "Double" and it can't be edited as it is linked table. this causing the pivot table to treat as if its not a number so i can get a count but when i use "sum" in the pivot table I get "0" instead of summing the number of products.

any ideas how can i fix this?

I have Office 2007.

A spreadsheet has links to an Access DB, where data is drawn into Excel from some 15 tables in Access. Each connection is defined with name of database, the table/query name and other details.

Now, if I want to link to another database, with the same structure but a different name or location in the file sturcture, the thing requires that I redefine each connection from scratch. I cannot just simply input another file name.

In My Connections ( in the My Documents-folder) each connectioin is in a .ODC file, where I in principle could change the path to the database.

Is there a smarter way?

I have an Excel workbook that is linked to an Access query and I want to be able to refresh the workbook an Access module.

If I open the Excel spreadsheet and run a macro that uses ActiveWorkbook.Refreshall the data is refreshed OK without error.

In my Access module I have set up a reference to Excel using Getobject("myWorkbook")as myXL and my code opens and makes visible the spreadsheet OK but myXL.ActiveWorkbook.Refreshall produces an error box titled "ODBC Microsoft Access Driver Login Failed" The database has been placed in a state by user xxx that prevents it from being opened or locked.

Since opening the spreadsheet manually and refreshing the data works without a prompt to login to the Access database I don't understand why such an error is poduced when I am replicating the same code in Access.

Any help appreciated.

Mike C

We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option but to have data collected in Excel and for Access to the the ultimate repository for it all.

Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that i will wpare you reading).

My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need to either import the worksheets into Access as tables or to link the worksheets.

With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people who have done both or either of these methods. I have used both in the past for more limited scope tasks.

At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the field.
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give my second workbook the same file name as when I set up the links and update the links so they now show the data from the second workbook. Then run the append queries again to get this second set of data into Access. And continue with however many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update the data in the linked tables in Access.

So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the process.

And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful comments about the two processes envisioned above and any other issues I may be missing.

Thank you in advance.
Robert

How do I link a excel form to an access table.

I have searched but not found what I am looking for. If I have missed something please point me in the right direction.

I get an excel file every couple of weeks with updated information, some deletions, some additions, and some things remain the same. What I need to do then is copy that into my Access table. The problem is the lists are so long I can not go through them and pick out what has changed so I therefore have to copy the entire excel table and totally replace the Access table. Is there anyway I can write a macro that will merge the new information from the excel file to the access table? If so please give as much guidance as willingly possible, I am new at macros.
Thanks

Hi

I have the following Sub Routine which exports data from an Excel Worksheet into an Access Table. This works perfectly if the table contains no data, but errors if it does. Is there anything I can add to this to allow the data to be appended to the table.

Sub ADOFromExcelToAccess_Core_Time()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=D:Database.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Core_Time_tbl", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("A" & r).Value
.Fields("Persno") = Range("B" & r).Value
.Fields("Name") = Range("C" & r).Value
.Fields("TmType") = Range("D" & r).Value
.Fields("TimeTyText") = Range("E" & r).Value
.Fields("Period") = Range("F" & r).Value
.Fields("Date") = Range("G" & r).Value
.Fields("Number") = Range("H" & r).Value
.Update
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

All help appreciated
Ginny

Hello All,

I am in need of some help on this one. I am trying to import a "user selected" excel file into an access table. I have been able to some of what I need using.

	Code:
	 
DoCmd.RunSavedImportExport ("Import-CPIMS-Belt-Report")

But this only works on my machine becuase I created the Saved Import.
However, because multiple users will be accessing this database on multiple computers the file path/location will likely change everytime. I need a way for the Import to pull the selected file and path from the user selected file. I think the solution lies in TransferSpreadSheet but I can't get it to work. This is what I have so far.


	Code:
	 
Private Sub btnCPIMSImport_Click()
Dim CPIMS As Object
Dim ExcelFilePath As String
Set CPIMS = Nothing
Dim CPIMSTable As String
ExcelFilePath = ""
 
'Open file dialog and wait for user to select CPIMS Report .xls or .xlsx
    Set CPIMS = Application.FileDialog(msoFileDialogOpen)
    CPIMS.Title = "Select CPIMS Belt Report"
    CPIMS.AllowMultiSelect = False
    CPIMS.Show
    With CPIMS
 
'If user does not select file open msgbox and exit sub
    If CPIMS.SelectedItems.Count > 0 Then
    ExcelFilePath = .SelectedItems(1)
    Else
    MsgBox "You didn't select a file"
    Exit Sub
    End If
    ExcelFilePath = .SelectedItems(1)
    End With
 
' Delete current records in tblCPIMS
    CPIMSTable = "Delete * from tblCPIMS;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL CPIMSTable
    DoCmd.SetWarnings True
 
' Import New MOL detailed list into tblCPIMS
    DoCmd.TransferSpreadsheet ,10 ,"tblCPIMS", ExcelFilePath, True
End Sub

The last line of code is what I am having problems with. I recieve an error meesage stating that tbl CPIMS does not have 'F1' and various other errors as I change the code.
In the Locals window ExcelFilePath = "D:Documents and SettingsScudderMy DocumentsMALS-14_Database_Belt_Report.xlsx"
Any help on this is greatly appreciated.

Thanks,
Scott

Hi Folks,

In building a access front-end that uses linked SQL Server 2008 tables, for testing and development purpose I used integrated security with ODBC DSN and then linked the tables I needed to the front-end selecting primary keys on the tables that required it for data entry to be possible.

I am at the point where I would like to roll this application out, but I don't want to create ODBC DSN instances on the machines and don't want to delete and re-create the links to the tables with the appropriate username and password for each data entry user (different security for different users).

Is it easily possible to somehow, someway link these few SQL tables with appropriate primary keys without having to create a ODBC DSN and with the username and password actually stored in code SQL security NOT trusted/integrated security.

An example would be the following:

SQL Server IP address: 155.155.155.155
SQL Database: ELEPHANT
SQL Table: TIGER
Username: John
Password: Citizen

If there is an easy way to do this, I would really appreciate any help with this! Would something like a DoCmd.TransferDatabase acLink work? I suppose depending on the method, I may need to drop and then re-create the link each time, but that shouldn't be a problem. A function I can load in a Autoexec would rock if possible. Some of the tables require primary keys, which I haven't a clue how to create in code.

Thank you very much for any help you can provide!!

Joe

Hi,

This is what I want to do:

1. I have a split form with a button. When I click the button, the table should open in a newly created excel file.

2. The user will make some changes to the data.

3. At this time, a pop-up box will appear in the access window saying something like, "Do you want to save the changes you have made in excel?"

4. If the user says OK, the data in the existing table will be deleted and the table that is open in excel will be copied over to access.

I have pieced together how to do steps 1 and 3. In step 4, I know how to delete the data and how to bring the excel data into access. But my code depends on me knowing the name and location of that excel file. This is where I am stuck. Can you help me with this? Like do you know where the temporary file is stored? Or any other ideas? Which is the best approach?

Thanks in advance


Not finding an answer? Try a Google search.