Linking Via ODBC To Line 100
Feb 9, 2006
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)
Steve
View Replies
ADVERTISEMENT
Jan 17, 2006
Hello.
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.
Regards.
View 3 Replies
View Related
Feb 6, 2014
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?
View 2 Replies
View Related
Jun 26, 2015
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?
View 2 Replies
View Related
Apr 29, 2006
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.
View 2 Replies
View Related
Jun 25, 2013
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
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.
View 12 Replies
View Related
Jun 5, 2014
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
View 6 Replies
View Related
Nov 29, 2014
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
[code]....
View 8 Replies
View Related
Nov 18, 2014
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.
View 14 Replies
View Related
Oct 18, 2014
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?
View 1 Replies
View Related
Jun 11, 2007
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?
View 1 Replies
View Related
Dec 1, 2005
Hi,
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?
View 1 Replies
View Related
Feb 9, 2006
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.
can anyone suggest other reasonf or this failure?
View 2 Replies
View Related
Mar 8, 2006
Hi
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:
"Too few parameters...expected 8"
Can anyone offer suggestions?
Thanks
View 1 Replies
View Related
Mar 17, 2006
I am running a report that is built up of several backend queries.
I have identified the query that is causing the ODBC Failed message to appear, but cannot identify what is causing the problem.
I have tried to recreate the queries starting form the base query (which works fine).
Cannot anyone suggest anything?
Thanks
Paul
View 1 Replies
View Related
Jun 21, 2006
Hi Everybody!
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?
Thank you for your help.
View 11 Replies
View Related
Aug 8, 2006
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.
View 5 Replies
View Related
Oct 19, 2006
I have a user that can only set up an Access ODBC driver using one listed as:
"Driver do Microsoft Access (*.mdb)"
They used to be able to set one up with listed as:
"Microsoft Access Driver (*.mdb)"
Any ideas as what the difference is and why it may have started preventing them from using the latter?
Thanks,
View 3 Replies
View Related
Mar 16, 2007
Good Morning,
Im trying to set an ODBC connection to automate the connection process.
The current code I have is:
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
MsgBox "Opening Connection...."
Set MainConn = wrkODBC.OpenConnection("Connection1", dbDriverPrompt, True, "ODBC;DATABASE=live; DSN=live;")
which works fine in bringing up a dialog box to choose the connection.
But what do i write in the database and dsn fields to enable the automation process.
See attached picture for the log in settings. All I have to do is press 'Connect' to start talking to the ODBC.
any help would be greatly appreciated.
Justin
View 6 Replies
View Related
Mar 21, 2007
Just a quick (hopefully easy) question.
Does anyone know if it is possible to link to Filemaker Pro tables for Mac, into Access?
The Filemaker DB will hold the data, with Access displaying the forms, along with some additional tables.
Does anyone have any ideas?
View 2 Replies
View Related
Jul 10, 2007
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
View 1 Replies
View Related
Aug 15, 2007
Hello...
Question, do you need one for your database and if so why? I have read what they are but I haven't found really why you would need it?
Don't know if this is the right place to post this question but wanted to get some input on this.
Thanks
R~
View 5 Replies
View Related
Feb 14, 2008
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.
View 3 Replies
View Related
Apr 24, 2006
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
strDatabase = DataBase_Now.Value
strTableName = "Tbl_A_Yles"
strConnect = "ODBC;Driver={SQL Server};Server=" & Server_Ip & ";DATABASE=" & strDatabase & ";UID=" & Server_User & ";PWD=" & Server_Pass & ";"
'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"
Thanks in advance.
View 1 Replies
View Related
Nov 27, 2006
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
T~
View 7 Replies
View Related
Jan 7, 2008
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
View 1 Replies
View Related
