I write a program which is querying some data from a MS-Access database.
The problem here is, that the tablenames contain characters like "/" or "-" and I
do not want to change these names, because I think that the database
structure will be damaged.
To solve this problem I thought of creating a view to the table. I tried creating
this with a SQL statement, but that did not work.
Is there any other possibilty to create a view on a MS-Access table?
I have run a query that reports the following information. I am using Access 2007
Model (Text) Start Time (Time) Step (1-5)
I have a report that is grouped by step and I have Force new page selected for this group. So the idea is that each page will show the models and start times for each step on separate pages.
Since the width of what I am showing is not very wide I want two columns so that when there is too much data the data will just continue in the next column rather than create a new page. This way all the information for each step will be contained on one page.
I am running into problems when there isn't enough data for a given step to fill up the entire first column. Access is placing the next data set (Step #2 for example) in the next column rather than on its own page. Does access view a new column as a new page? I thought that selecting "Force new page" for my group would force a new physical page and not just dump the next data set into the next column.
I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.
Before anyone suggests it, yes I have posted this on SQL server forums.
declare @TheName char (8) ,@Cmd char (500)
select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)
I'm experimenting in MS Access VBA reading from one MS Access application to another as visible in the code below. My problem is that I'm reading a recordset from an Access file and want to take that recordset (rec) to create a table in the current databank. If I run an cmd.commandtext "make table query" it runs the query across the connection and places the table in the remote database (as expected). Can I take the recordset and create a local table using ADODB command or is there a better approach. (I should note that the remote MS Access file does not have an .mdb suffix) Thanks in advance.
Sub get_indbyind()
Dim strInputfile As String Dim dlgOpen As FileDialog Dim bob As String bob = Application.CurrentDb.Name
' select connection Set dlgOpen = Application.FileDialog(msoFileDialogOpen) With dlgOpen .AllowMultiSelect = False .Show End With strInputfile = dlgOpen.SelectedItems.Item(1)
' make connection Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection Dim strcnn As String strcnn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & strInputfile cnn.Open strcnn ' MsgBox "connection made"
' create recordset Dim rec As ADODB.Recordset Set rec = New ADODB.Recordset rec.Open "SELECT * FROM SATransfers;", cnn
Dim n As Long Dim i As Long
Do While Not rec.EOF Debug.Print rec.Fields(0).Value; rec.Fields(1).Value; rec.Fields(2).Value; rec.Fields(3).Value rec.MoveNext Loop
' create table from connection cnn Dim cmd As ADODB.Command Set cmd = New ADODB.Command Dim rs As New ADODB.Recordset cmd.ActiveConnection = cnn cmd.CommandText = "SELECT SATransfers * INTO tmp1 FROM SATransfers;" Set rs = cmd.Execute
cnn.Close Set cnn = Nothing Set cmd = Nothing End Sub
Hi i hope someome can help. I am importing a data into a table, records are being rejected as they should due to validation rules. However access is not creating a table of rejected records. What am i doing wrong. Thanks for any help
I have currently been given a task to re-work some current coding within Access. This is well above my understanding of Access and was hoping to get a little assistance with some simple coding language.
The database I am manipulating has data collected from tourists about their destinations and origins.
I wish to create 2 tables based on a current table. One table will be short trips (trip length <= 150km) and the other long trips (trip length < 150kms)
I wish for these tables to be created from a table of raw data upon the click of a button (GUI interface).
If you are puzzled by my question. I'll try to make it simplier by asking:
1. what line of code will create a new table overriding old 2. what type of code is required to create this table with specific column headings? 3. How do I import information from current available tables with the condition: 'Row Y' is > X
I have just upgraded from Access 2003 to 2010. Now I'm trying to relearn some of the small things I used to be able to. In 2003, I could just have a table highlighted and select "Insert, Query". It would then open a query design with that table. Is this possible to do this in 2010? Or do you have to open query design then add the table manually?
Hi, can someone tell me if there is a way to save several different datasheet views? I have a large database with many fields. I use forms for data entry, but to view the data I use the table datasheet (so I can see multiple records at a glance). But every time I have to manually move, freeze, and hide columns to get the view I want!! Is there a way to save several different datasheet layouts that I can access the view I want with one click? because I am tired of freezing/hiding columns every single time.
In Access can I link to a SQL Server view? I am currently linking to the SQL Server tables in my database and don't have any views. But if I can link to views I could give the workgroup permissions in Access to the views, not to the primary tables. This would provide some added security from the user who bypasses my switchboard forms and goes to modify the Access tables directly.
I am trying to remove the built in form views from access 97. Is it possible to remove the navigational functions of the form while in view mode (ie first record, previous record, search, next record, and last record)? There is also an arrow bar that has no functionality to it placed at the left side of my forms that I would like to get rid of. Is this possible? Thanks for your help!
Is it possible (haven't found a way yet) to have two "split subforms" on a parent form side-by-side? When I try the multiple rows portion of the split view I created as a subform doesn't appear?
Want to have a form when I have search/filter/select record for table 1 (on the left) and table 2 (on the right) with a means in between to create a relationship which has a few parameters.
Does anyone have any experience of writing to an Access database via Web.
Do you think it is feasible, for an online entry form, to have 'customers' input their details or wahtever, then this info is sent to some sort of flat file?CSV is it called?
Then the admin can check these, then import to DB quite easily?
Just been upsizing an Access DB. It seems to create views and functions for normal select statements and stored procedures are only created for insert or update statements. As fas as a recent DBA told me, he said that views should be used sparingly as they are not very efficient. Is this the case?
I also don't see why you would create a function when you could use a stored procedure to so the same SELECT statement(s)?
Front end access 2010, back end SQl-server 2008 R2.I have all talbes and views connected to the server and due to incorrect results I found out, that the views from the server deliver incorrect data.Of course I refreshed the views in the access front end I even deleted them and connected them for new, but the incorrect data, even a different field sequence (!) remain.in a certain field there can be two different values: 1 and 2.
If opening the view I can only see 1, on the server the view shows 1 and 2, in the records, whatever is the value.This is not a calulated field.When checking this in a query in access I can select on value 2 (although I cannot see it!!) and I get the correct records but in the field itself the values still show as 1 !!
Same with other values in other fields. When I take the same SQL from the view in a stored procedure and execute is from access I get the correct data!!On both, view and sp I have the same permissions on the server!why views show incorrect data?
I have a text source file and inpul layout i.e. field names start and end positions in excel file. I want create a table in access from the text data using excel file layout.
Can you please help me out in this. I am a mainframe programer and recieved an request to work in access.
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words. tblWords numWordID txtWord
Then I have some entries, all more or less synonyms of each others fresh new clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
I have created an Access Form wherein i take the user input in various text boxs and text areas.
Now plz help me to find out how to create Microsoft Word Document automatically from the information which the user has submitted.
I have provided a button named submit which when clicked will submit the information into the Access Database and using that data i want to create a Word Document into a particular format.
What is the way in which this could be achieved?
Please help me out to find a solution to this problem.
Hi, I ve got an access db thats attached to a java application. I need to be able to create a temporary table that stores a result set, its necessary to do this as the java app needs to perform lots of repeated operations on this temp table.
I was think something like this would work:
DROP TABLE tempResults CREATE TABLE tempResults AS SELECT col1, col2, col3 .... FROM orginalTable
So i tried this:
Create table temptable AS SELECT education, FROM supermarketTable;
but it doesnt work, access complains about syntax error in create statment.
what i am trying to do is have something create a new table. It could either be when a check box is clicked. but preferably when a name is entered into a text box. i just need the sub command to write. i tried using what access help has: SyntaxCREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])The CREATE TABLE statement has these parts:Part Description table The name of the table to be created. field1, field2 The name of field or fields to be created in the new table. You must create at least one field. type The data type of field in the new table. size The field size in characters (Text and Binary fields only). index1, index2 A CONSTRAINT clause defining a single-field index. multifieldindex A CONSTRAINT clause defining a multiple-field index but i keep getting an error. if someone could just give me a sample code that i could copy and paste into the code builder to get a simple table with one or 2 coloums i could edit to the specific things i need. i just dont know where to start. thanks for the help.
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
Iam new to access and having problems create database tables and their relationships, from the following details without duplication.
Tape Identification Numberà unique tape identity Date -à the date the backup is done . Backup type à either Daily or weekly or monthly or Full System or Yearly or Special Duration à either automatic or Manual Technician name à Person performing the Backup Comment à comments about the Tape’s history Location à tape is either located onsite or offsite. Number of use à Number of times the tape has been used Restoration à tapes restoration Details, either yes/no
Requirements
1.perform backup everyday 2.Perform restoration everyday 3.Search for tape from offsite storage a.Tape should be located offsite b.If it was used for daily backup, it should be at least 60 days old from current date. c.If it was used for Weekly backup, it should be at least 6 months old. d.If it was used for any other backup type, do not use.
