Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am having problems inputting data from .csv file into an Access 2010 temporary table.  The data is output from a web application called Clicktools. Please refer to the enclosed database file:Testinput.accdb and the function doimport() in Module1.

The enclosed file Testfile1.csv contains a single comma delimited record (Andrew Everyman), where the last field is a mixture of alpha and numeric characters. The field in the Access table tmpDDPaymentsData is defined as Text(255).

If you run the doimport() function  all required fields will be imported correctly. However if you change the file name in the doimport() code to import Testfile2 (which contains the same record mixed in with other records where the last fields are all numeric characters) the import routine will not load the last field of the Andrew Everyman record. All other records load correctly.

Thanks for your help

Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I'm running MS Access 2016, and I'm looking for a function that will do the following when closing a form in my database.

1. After active form closes, check if there are any other open forms
2. If there are no other open forms, fire a command such as
MsgBox "All forms have been closed."

Open in new window

I appreciate any expert advice you can provide! =)
I'm looking at partitioning the following tables because performance has been getting worse as we add more customers.  We only keep 90 days of data and it's always queried for one customer at a time, so I'm looking at partitioning it by customerID.  

SessionClosed   rows:  378,768,648  MB: 61,233
Access   rows:  112,975,561  MB:  26,323
SessionDenied  rows:  99,010,046  MB: 16,790

I've never done this.  Here are my questions and concerns:

1. How many files and/or filegroups should I create?  They will not be spread across multiple disks.  Currently we only have the one primary file group with just the 2 mdf and ldf files.

2. There are currently 32 customers and growing each w/average of 12 million records each.  The CustomerIDs range from 1 to 500.  How do I create the partitions for the random customerIDs?  Will I have to manually add partitions each time a customer is added?  Should I create 500 partitions?  Should I break them up into 5 partitions (1-99, 100-199, etc.)?  In that case would I have 5 filegroups with 1 file per?

Thank you!
I am trying to build a small web application in that texts our employees when we have an urgent message that we need to distribute.  My idea is that someone with the authority to send one of these messages will login, create a message, and send it.  I have been able to send messages successfully if name a single mobile number to a variable, or even when I assign multiple numbers to an array.  Of course I don't want to go to the code to add/remove numbers, so I have a database with the numbers in it.

This is the part that is frustrating me is that I don't know how to get my data from SQL into my application.  The closest I have gotten appears to assign the current location to my array, I am not even sure how that is happening.

[EDIT: the data I am trying to get from SQL is a single column of integers representing phone numbers.]

Here is my code asking SQL for data:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace ProjectASPudemy.Pages

    public partial class Comms : Page
        private const string cs = "Data Source=ElRando\\SQLE;Initial Catalog=Inventory;Integrated Security=True";
        private const string sql = "SELECT CellPhone, LastName FROM Employees WHERE CellPhone IS NOT NULL AND CurrentEmployee = 1";
        private const int V = 0;

        protected void Page_Load(object sender, 

Open in new window

Dear expert team

I I have two date field, one is registration date and one is exam_date. I need to fill data in a field called work_date field as follow:


1)      If registration date = exam date, then Exam date to be entered in work date as date value

2)      if exam date is next day from registration date, then put registration date in work date

3)      if registration date is same as exam date, but between the hours 12 am to  4:am, then put the date before registration date

4)      if exam date more than one day off, follow same system above

Please help, I attached the database
If you have any automated processes for populating data in your databases outside of the standard means (users adding/updating/removing records via a front end application), do you have any specific processes in place to monitor the 'feed' from say an external database for success. I am wondering how and what features in MSSQL (or something else) you would use to check data feeds from external sources have worked if they are set to feed data say on a nightly basis.
Unable to upgrade SQL Server from SP1 to SP2.  The database instance is greyed out.
Current version: 13.0.4001.0
Upgrade file being used: SQLServer2016-KB4524334-x64.exe
This file has worked on other instances on the same SQL server and brings those instances up to 13.0.5492.2
The instances that successfully upgraded were at 13.0.5026.0
The service pack installer does see the 13.0.4001.0 instance, and shows it unchecked for the current upgrade, but the checkbox is greyed out and can't be checked.
As you can see from my screen shot, I’m using the following sub routine to link various worksheets from ExcelFile.xlsx into my database. As shown below, I’ve linked only four worksheets. However, my actually worksheet has many more tabs that I need to link. Also, the tab names are not constant. In other words, I could have three worksheets named like: 1111, 2222, 3333. Then at a later time for the exact same file, I could have four worksheets with multiple other names like: AAAA, BBBB, CCCC, DDDD.

Right now, it’s a manual process for me to update this code each time a sheet name changes or is removed. I’d like to modify this code to be more dynamic by accomplishing the following:

1.      First delete all database table objects that have a string of 4 characters in their name (e.g. 1111, 2233, AABC)
2.      Link all worksheet tabs that have a string of 4 characters in their name from ExelFile.xlsx

I'm using MS office 2016. Any Expert suggestion on this is greatly appreciated! =)

Private Sub LinkSpreadSheets()

Dim Fpath As String, _
  XLname1 As String, _
  tb1 As String, _
  tb2 As String, _
  tb3 As String, _
  tb4 As String

Fpath = Environ("USERPROFILE") & "\Documents\databases"

XLname1 = "\ExcelFile.xlsx"
tb1 = "1001"
tb2 = "2001"
tb3 = "3001"
tb4 = "F008"

With DoCmd

    .DeleteObject acTable, "1001"
    .DeleteObject acTable, "2001"
    .DeleteObject acTable, "3001"
    .DeleteObject acTable, "F008"

    .TransferSpreadsheet acLink, , tb1,

Open in new window

I'm trying to generate a database query (sql server 2012) of the 50 most recently search items for a particular user. All of the data is in a table called LogSearch, however, I cannot use a simple DISTINCT query because the SearchDate will always be unique. This is what I started with:

Select distinct top 50 SearchNumber, SearchDate from LogSearch where [REMOTE_HOST]='xxx' group By SearchNumber, SearchDate

What would be the best way to approach this? I've looked into CTE's, but am not sure if that's the best way to go. Any suggestions would be greatly appreciated!
this was working, i change the call to the database to use the Parameters element of ADO as just passing the values failed when passing HTML to database..

i have obviously done something wrong but been looking at this now for 4 days and not got any closer to a solution..

your help would be a god send
Thank you

DIM cmd, DbStr
DbStr = "User ID=xxxx;Password=xxxx;Initial Catalog=CMMS;Data Source=MYCMMS;Provider=SQLOLEDB"

Set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=DbStr

'Prepare the stored procedure
        cmd.CommandText = "ConfUpdate"
        cmd.CommandType = adCmdStoredProc
	cmd.Parameters("@Company") = MyCompany
	cmd.Parameters("@address1") = Myaddress1
	cmd.Parameters("@address2") = Myaddress2
	cmd.Parameters("@city") = Mycity
	cmd.Parameters("@county") = Mycounty
	cmd.Parameters("@postcode") = Mypostcode
	cmd.Parameters("@phone") = Myphone
	cmd.Parameters("@WorkorderEmailTPL") = MyWorkorderEmailTPL
	cmd.Parameters("@VendorEmailtpl") = MyVendorEmailtpl
	cmd.Parameters("@InstanceID") = MyInstanceID
	cmd.Parameters("@VersionNo") = MyVersionNo
	cmd.Parameters("@updatedBy") = MyupdatedBy
	cmd.Parameters("@IssueDate") = MyIssueDate
	cmd.Parameters("@IssuedBy") = MyIssuedBy
	cmd.Parameters("@LiveDate") = MyLiveDate
	cmd.Parameters("@emailfooter") = Myemailfooter
	cmd.Parameters("@emailDisclaimer") = MyemailDisclaimer
	cmd.Parameters("@action") = Myaction
	cmd.Parameters("@confID") = MyconfID


Open in new window

OWASP: Avoiding Hacker Tricks
LVL 19
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

I can get Date variable to match sql server date to use in code to find indexed line in database table.  I have tried multiple formatting options - nothing works.  I need to include miliseconds in the Date format - not in string format.  I use this in a <= operations to compare date to the database.  When it is = it uses that indexed line to include in the charted data.  Everything I have tried gives me date and time without miliseconds.

I have tried the following.

Dim MaxDate As Date = DataTable.Rows(0)(0)
MaxDate = Date.ParseExact(DataTable.Rows(0)(0), "yyyy-MM-ddTHH:mm:ss.fff", System.Globalization.CultureInfo.InvariantCulture)

I can convert using a string variable, but cannot use string variable in query search for date.
would SQL server have any form of log file which would list when a backup of a database was restored, and who performed the restore operation? I wasn't sure if it would be stored in a table or elsewhere, or where even to begin to look?
I am recreating a page that has searches a database for medical providers three different ways. The page was originally written in and SQL. i am recreating it in PHP and MySQL. Below is the page I am recreating.

Below is the data from the database that needs to be displayed for each of the areas one can search by.

provider_physician, provider_name, provider_address, provider_city, provider_state, provider_zip, provider_phone, provider_website

Below are the three different ways one can search the database.

Method 1 - Enter the name of a physician or provider
Method 2 - Search by specialty
Method 3 - Search by city

I don't understand how to add more columns to display in my search results using jQuery. I am a novice at jQuerry and need to find out how to add an additional field. If I can understand how to add one more field, I should be able to add the rest.

Lastly, how do I get the results from Name search to display the same way the results from the specialty and city do?

I have enclosed all of my files along with the database I am pulling the results from.

Thank you in advance for your help!
Hi, I have 3 flat file sources. Now I want to count the number of records in each flat file and I want to write the counts to a flat file in the below output.

FileName  Count

can anyone suggest me step by step on how exactly I achieve this ?
Many Thanks
Hi Team,

Currently , iam woking on Miscrosoft SQL Server database . Iam new to this . I want help  on datadictionary views like we have in oracle to capture the table , table column information we have in oracle (user_tables / user_tab_columns).

I was told i can get it using any system stored procedure or some views. But able to find one . Can anyone help me with this information in Microsoft SQL SERVER .
Hi Experts,

I have a web page (designed in Caspio), that is meant for users to change their password.
Would like to add a feature that stores the date that password was changed in the database.
Created a field for that, now I need the JavaScript code to update that field upon change of PWD field.
Attached is how the page looks like.

I have a working SQL Command.  I want to create a universe using the SAP Information Design Tool 4.2 and import my SQL Command.  Is that doable?  If so, please tell/show me how.

According to this site, the You can now connect to your Oracle Database from PowerApps, Flow and Logic Apps. The Oracle Database connection allows you to list tables, and perform standard create, read, update and delete of rows in an Oracle databases. In addition, it supports full delegation of PowerApps’ filtering, sorting and other functions. It does not support triggers or store procedures yet.

The article was written in March 2017. Does the connector support triggers and stored procedures now?
Using SQL server (up to version 2017) how can I find values where they are present against the distinct values of another field in the same table?  I will also need the ability to exclude one of the distinct values from consideration.
Here is an example of results expected given 3 different data sets and with the 3rd data set showing an exclusion applied :
Example results imageSQL file for recreating table and data for example 3 and also the Excel file of the data examples is attached.

 Performance is important in my solution and if it is not viable to achieve great performance with the data stored in this way I can alternatively work on the approach of building a separate temporary table per category and then joining all the tables which should leave me with the same result.  
The two columns together will be a unique combination and could form a compound key if that makes any difference to the options.

Thanks in advance!
Exploring SharePoint 2016
LVL 19
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.


    I look at Maximum Capacity Specifications for SQL Server.The rows of every table is limited by available storage.If rows count of some table is over 2554108 in database for now and the rows of some table has continued to increase so fast. Should I be worried about that?

I'm working with a fully functional Node.js code that interacts with the Fitbit API.

Being somewhat new to Node, however, has me in a spot where I need a little guidance.

The code looks like this:

// initialize the express application
const express = require("express");
const app = express();

// initialize the Fitbit API client
const FitbitApiClient = require("fitbit-node");
const client = new FitbitApiClient({
	clientId: "CLIENT_ID", //client_id
	clientSecret: "CLIENT_SECRET", //client secret
	apiVersion: '1.2' // 1.2 is the default

// redirect the user to the Fitbit authorization page
app.get("/authorize", (req, res) => {
	// request access to the user's activity, heartrate, location, nutrion, profile, settings, sleep, social, and weight scopes
	res.redirect(client.getAuthorizeUrl('activity heartrate location nutrition profile settings sleep social weight', 'http://localhost:3000/callback')); //callback url

// handle the callback from the Fitbit authorization flow
app.get("/callback", (req, res) => {
	// exchange the authorization code we just received for an access token
	client.getAccessToken(req.query.code, 'http://localhost:3000/callback').then(result => { //callback url
		// use the access token to fetch the user's profile information
		[b]client.get("/profile.json", result.access_token)[/b]
		[u]//client.get ("/1/user/[user-id]/[resource-path]/date/[date]/[period].json", result.access_token)[/u]
		.then(results => {

Open in new window

how can i integrate excel with access? For example, We have a database file (.accdb) on a sharepoint, that I would like to pull data from into Excel systematically (call it once a day). I then want to use that data and run various analytics off of it/generate reports.
I guess this is a very elementary question but I would like clarification.  In the following query as part of a stored procedure a field in tblRotaLineSummary is getting updated

update tblRotaLineSummary
      Set AtF = C.MOUT
      from tblRotaLineSummary RLS
      inner join
      (select payrollid,DateAdd(d,-2,Convert(datetime,Max(OutTime))) as MOUT from #CIOR
      Group by PayrollID) C
      on RLS.staff_id = C. PayrollID
      where RLS.DS is null and RLS.AtF is null and RLS.AtS is not null and RLS.rota_id=@rota_id and RLS.rota_date=@rota_date

my question is, I have applied the criteria to the alias (RLS), is this ok or do I need to apply the criteria to the non aliased table as well or can it be either.
I need to calculate number of days, adjust it, and then use it to compare on embedded SQL statement. Details attached.  

Thank You in Advance!
Lynn days-calc-with-caveat.docx
Good morning,

I'm doing some initial analysis of a MS SQL database.  My process is below.

First I run:


Open in new window

Then I scroll through the column names looking for interesting columns and run a frequency as follows:

SELECT IntersetingColumn, COUNT(*) AS Frequency 
FROM TheTableImAfter
GROUP BY InterestingColumn
ORDER BY Frequency

Open in new window

In this database I have a column name that's returned by the first select that doesn't appear to actually be in the table.  The error message is:

Msg 207, Level 16, State 1, Line 136
Invalid column name 'A_ColumnListedInInformationSchemaColumns'.
Msg 207, Level 16, State 1, Line 134
Invalid column name 'A_ColumnListedInInformationSchemaColumns'.

I'm sure I'm missing something simple.  Any ideas?







Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.