Hi all,
I am a newbie to the forum and dont know much about it yet so please point me in the right direction if i am doing anything wrong..
My task is to create a spreadsheet that will automaticaly update on opening. The data i require is in Sage Line100 which is very difficult to work with(for me with limited database knowledge).
Because i need to link tables i thought that the best way would be to create a link with Access and create the report format that i need so that it could be exported to Excel.
1: Is this possible(or am in dreamland)
2: How do i refresh the data or is it possible to make it dynamic
3: how do i create a report that saves as an excel file and refreshes when opened
Thank you for any help that can be provided(bowing down to superior knowledge)
When linking a table via ODBC I usually get asked to pick a field that contains unique values. This is all very well but I have hundreds of tables to link to across a slow network and would like to leave it running for a few hours. With the current set up, this is not possible and I have to keep clicking on ok every few seconds/minutes. Is there anyway to avoid this message so I can just leave it running until it finishes.
I'm nearly at the point of distributing my database. I have been writing this on my laptop and I need to get it working on a Terminal Server and Data Server at the data centre that host our system which is a remote location. The Terminal Server has Access Runtime only, not the full version of Access. This obviously presents me with a bit of a quandary as I still need to be able to maintain the software but do not want my laptop to link via the web to the database to do so. My solution is to take a copy of the database onto my laptop when I need to do anything, which is not a problem. My issue is that I'd like a simple way of maintaining the link between the Front end and the Back end so that if I upload a new copy of the back end or the front-end it will not throw the toys out the pram at the users!
I understand that I am best to put the back end onto the data server and to then distribute the front end to each user. Each user has their own account on the Terminal Server so I can put the front end into their userprofile or something similar (if it is true that I can't just give them all shortcuts to the same front-end that is?!).
I would like to try and get the back end to link to the front end via ODBC as if this is the case I can use the File DNS path on the terminal Server so each user has access and can use File DNS on my laptop when I need to do any updates on my copy so that I am not having to do anything too complicated with the linking of the front and back end due to the differing file systems. Is this possible to do or am I completely going at this the wrong way and missing an easier method?
I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?
On a webserver certian files are stored above the root directory. Therefor the user of the website can never access those files directly. But he is utlizing that file.
For example: We use Hotmail to see our mails. but we never download the complete database.
So is there a method that we can secure the backend using roaming profiles or something else so the user has a link to the backend but cant directly access the backend.
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line with me.graph47 .SeriesCollection(1).border.Color = vbblue ' change the line color .SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3 .SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4 .SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color, .SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
We are using Access 2000 as our front end and SQL 2000 as the back end. We use ODBC to connect to the SQL database. One of my access 2000 programs was getting a little sketchy. So I created and new access 2000 database and imported all the modules, macros, reports, queries and tables. That all worked just fine accept now every time I open the new database it ask me to log in, I think it’s the ODBC connection. I have run table link manager, but it still asked me to log in. I have six other Access databases and none of them ask me to log in. So what am I missing?
Requirement - To generate a report from a remote database
Methods opted - 1. Excel - Connect remote database via odbc connectivity for the required query - Excel generates the report and it can be refreshed when required
2. Access - Connect remote database via odbc connectivity using link tables - Generate report by writing a query on the linked table - Since it is linked, it gets refreshed automatically
I want to go for ms access option since database is increasing day by day and excel will not be hold it.
But linking tables takes more time. Can you please suggest to extract data from remote database without linking tables?
It is not required to link the database in real time ie., No automatic refresh of data is required. Periodic refresh is enough. Not the whole table is required to be linked. Query which returns current year data from the database is enough to generate the report. So, is there anyway to link query in ms access? Please let me know how to connect remote database fastly?
i am using access as a front end to SQL Server 2005 and link tables via ODBC.
Today when I run a query (normally works fine) I receive an ODBC Failed message box. I have tried opening each link table connected to the query and can open fine.
I'm using Microsoft Query to get data from a MS Access database into MS Excel. It seems that whenever I pull data from a UNION QUERY or SELECT QUERY based on a UNION QUERY I get an error message:
I am creating an Access database that will be used by multiple users. This database will be on the network and will be the front end of an oracle database connected to it via ODBC. Do I have to set up the ODBC to oracle only on the machine that hold the database on the network or do I have to set up an ODBC on each user's machine?
I am having a problem with an access database that is connecting to a sql server. When trying to run any queries it is very slow. I think the problem might be caused by the ODBC link and I was wondering if there are any alternatives to using ODBC to connect access and sql.
I use dsn file as below from Access to link to SQL server via ODBC. From Access, I use this file but list all tables including system tables. How to restrict system objecs?
[ODBC] DRIVER=SQL Server UID=aspfun Trusted_Connection=Yes DATABASE=credent WSID=aspfun_ws APP=Microsoft Data Access Components SERVER=order
My program is an Access 2003 database that connects to SQL 2000. I have a continuous form that shows between 10 an 25 records. I have coded an update button that when clicked updates a check box on each record displayed and then updates the form. Normally this works perfectly. However, sometimes when the button is clicked nothing happens for aproximately a minute then the user gets the error ODBC - call failed and no records are uodated. I have tried resetting the view from Dynaset to Dynaset (Incosistent Updates) and extending the timeout to 300. I have also tried extending the MSAceess Page timeout setting to 5000 instead of 5 under ODBC. Nothing has helped. Does any have any other solutions? Or a way to get a more specific error. I am really just guessing that it is a time out issue.
Hi i would like to make a question about ODBC i have created an access database that connects via ODBC to an SQL server database, with the help of someone else i have created this code that works well for the link of 1 table that i choose to link the problem is that i want to link all tables from database and not doing it 1 by 1.
Is there a way to link all tables from database ???
The code i'm using is :
Dim strConnect As String Dim strDatabase As String Dim strTableName As String
'Checks to see if table exists. If it does, delete it. If TableExists(strTableName) Then DoCmd.DeleteObject acTable, strTableName
'And then use the trusty old TransferDatabase method to re-link the table declared in strTableName from the SQL Server DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, strTableName, "dbo_Tbl_A_Yles"
I want a linked table in my current database. The server where the other access database is already has a ODBC connection created. How to I create a linked table to that other Database through the odbc? I know this is very unspecific, but I do not know what to do on either end. Thanks
I wonder if anyone had any suggestions regarding how to link 4 ODBC tables so the info in each of the 4 ODBC tables are in one table. The 4 ODBC tables are linked in from a mapping system. Each table has 80% the same fields with some minor differences. i dont know if a union query would be the best answer in this case as the field in each of the ODBC's tables do not exactly match. Some have 12 fields, some have 14 fields. Any help would be appreciated? also this would be using Access 2003 if you would like to know. thanks
